Shulou(Shulou.com)06/01 Report--

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 |

+-- +

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.