在索引字段上使用函数,该字段的索引将会被抑制。如下案例:
查看表结构:
使用函数进行查询:
不使用函数:
可以看出:查询时间变快很多。
对比一下执行计划:
查看表结构:
点击(此处)折叠或打开
- mysql> show create table test06 \G
- *************************** 1. row ***************************
- Table: test06
- Create Table: CREATE TABLE `test06` (
- `id` bigint(11) NOT NULL DEFAULT '0',
- `u_id` bigint(11) NOT NULL,
- `openid` varchar(100) DEFAULT NULL,
- `unionid` varchar(100) DEFAULT NULL,
- `username` varchar(100) NOT NULL,
- `password` varchar(100) NOT NULL,
- `create_time` datetime NOT NULL,
- KEY `idx_test03_id` (`id`),
- KEY `idx_test03_name` (`username`),
- KEY `idx_test06_crea_time` (`create_time`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
点击(此处)折叠或打开
- mysql> select count(*) from test06 where date(create_time)=curdate();
- +----------+
- | count(*) |
- +----------+
- | 0 |
- +----------+
- 1 row in set (1.00 sec)
点击(此处)折叠或打开
- mysql> select count(*) from test06 where create_time=date_format(curdate(),'%Y-%m-%d');
- +----------+
- | count(*) |
- +----------+
- | 0 |
- +----------+
- 1 row in set (0.03 sec)
对比一下执行计划:
点击(此处)折叠或打开
- mysql> explain select count(*) from test06 where date(create_time)=curdate();
- +----+-------------+--------+-------+---------------+----------------------+---------+------+---------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+--------+-------+---------------+----------------------+---------+------+---------+--------------------------+
- | 1 | SIMPLE | test06 | index | NULL | idx_test06_crea_time | 5 | NULL | 2009559 | Using where; Using index |
- +----+-------------+--------+-------+---------------+----------------------+---------+------+---------+--------------------------+
- 1 row in set (0.00 sec)
-
- mysql> explain select count(*) from test06 where create_time=date_format(curdate(),'%Y-%m-%d');
- +----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
- | id | select_type | table | type | possible_keys key | key_len | ref | rows | Extra |
- +----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
- | 1 | SIMP | test06 | ref | idx_test06_crea_time | idx_test06_crea_time | 5 | const | 1 | Using index |
- +----+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30135314/viewspace-2144996/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30135314/viewspace-2144996/