Alex Rivera | Logout

What's the difference between !col and col=false in MySQL?

Asked 2010-03-25T16:08:55.410
9

The two statements have totally different performance:

mysql> explain select * from jobs  where createIndexed=false;
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
|  1 | SIMPLE      | jobs  | ref  | i_jobs_createIndexed | i_jobs_createIndexed | 1       | const |    1 |       | 
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql> explain select * from jobs  where !createIndexed;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | NULL          | NULL | NULL    | NULL | 17996 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

Column definition and related index for aiding analysis:

createIndexed tinyint(1) NOT NULL DEFAULT 0,
create index i_jobs_createIndexed on jobs(createIndexed);
Edit
Report

1 Answer

3

MySQL cannot use the index for WHERE !createIndexed, because it needs to evaluate NOT createIndexed for each row, with a table scan.

answered 2010-03-25T16:16:33.867

Your Answer