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

Question

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);

Answer 1

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

Answer 2

Logically, these operations are the same, but MySQL's optimizer is just not so smart to see createIndexed = 0 in NOT createIndexed.

FALSE in MySQL is just a synonym for 0 and TRUE is a synonym for 1.

This condition is false:

SELECT  2 = TRUE

--
0

, so the first query is just a pure index ref comparison to 0 which MySQL is aware of, while the second one contains more complex logic that MySQL cannot represent as a sargable expression.

Answer 3

I think the difference lies in the handling of null values - (regardless of the NOT NULL statement in your case). Maybe these manual sections can help?

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html#operator_not

Answer 4

In MySQL, the FALSE keyword is not a boolean piece of data: it's an integer constant that equals zero. On the contrary, ! (aka NOT) is a logical operator that:

Evaluates to 1 if the operand is 0, to 0 if the operand is nonzero, and NOT NULL returns NULL.

I suppose that there's not much practical difference:

mysql> select 1=0, 0=0, 33=0, null=0, not 1, not 0, not 33, not null;
+-----+-----+------+--------+-------+-------+--------+----------+
| 1=0 | 0=0 | 33=0 | null=0 | not 1 | not 0 | not 33 | not null |
+-----+-----+------+--------+-------+-------+--------+----------+
|   0 |   1 |    0 |   NULL |     0 |     1 |      0 |     NULL |
+-----+-----+------+--------+-------+-------+--------+----------+
1 row in set (0.00 sec)

Yet they're not identical operations.