Fields are suppressed using functional indexes
Use the function on the index field and the index of that field will be suppressed. The following are examples:
View the table structure:
Click (here) to collapse or open
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 uses functions to query:
Click (here) to collapse or open
Mysql > select count (*) from test06 where date (create_time) = curdate ()
+-+
| | count (*) |
+-+
| | 0 |
+-+
1 row in set (1.00 sec) does not use functions:
Click (here) to collapse or open
Mysql > select count (*) from test06 where create_time=date_format (curdate (),'% YMY% MMI% d')
+-+
| | count (*) |
+-+
| | 0 |
+-+
1 row in set (0.03 sec) shows that the query time is much faster.
Compare the execution plan:
Click (here) to collapse or open
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 (),'% YMY% MMI% 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 |
+-- +
