Aug 10 2009
MySql drove me crazy!
Today, I was trying to optimize some mysql query in our experiment framework.I came across to two query explains that I cannot understand.
Here are the two queries:
explain select * from TABLE_NAME where created > ‘2009-08-09 19:10:44′
explain select * from TABLE_NAME where created > ‘2009-08-09 20:10:44
′and here are the explain results:
mysql> explain select * from TABLE_NAME where created > ‘2009-08-09 19:10:44′;
+—-+————-+——————+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——————+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | TABLE_NAME | ALL | ou_created | NULL | NULL | NULL | 1226122 | Using where
|+—-+————-+——————+——+—————+——+———+——+———+————-+
1 row in set (0.00 sec)
mysql>explain select * from TABLE_NAME where created > ‘2009-08-09 20:10:44′;
+—-+————-+——————+——-+—————+————+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——————+——-+—————+————+———+——+——–+————-+
| 1 | SIMPLE | TABLE_NAME | range | ou_created | ou_created | 4 | NULL | 206400 | Using where |
+—-+————-+——————+——-+—————+————+———+——+——–+————-+
1 row in set (0.00 sec)when you change the parameter you search, mysql changes query plan in a wrong way,interesting, isn’t it ? (fyi, my mysql version is: 5.0.51a)
