MySQL inconsistent table scan results
- by user148207
What's going on here?
mysql> select count(*) from notes where date(updated_at) > date('2010-03-25');
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.59 sec)
mysql> select count(*) from notes where message like'%***%' and date(updated_at) > date('2010-03-25');
+----------+
| count(*) |
+----------+
| 26 |
+----------+
1 row in set (1.30 sec)
mysql> explain select count(*) from notes where date(updated_at) > date('2010-03-25');
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | notes | ALL | NULL | NULL | NULL | NULL | 588106 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.07 sec)
mysql> explain select updated_at from notes where message like'%***%' and date(updated_at) > date('2010-03-25');
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | notes | ALL | NULL | NULL | NULL | NULL | 588106 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.09 sec)
mysql>
