MySQL inconsistent table scan results
Posted
by user148207
on Stack Overflow
See other posts from Stack Overflow
or by user148207
Published on 2010-03-26T21:24:43Z
Indexed on
2010/03/26
21:53 UTC
Read the original article
Hit count: 272
Filed under:
mysql
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>
© Stack Overflow or respective owner
