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

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 2

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