What's the difference between !col and col=false in MySQL?
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:
createIndexe开发者_运维知识库d tinyint(1) NOT NULL DEFAULT 0,
create index i_jobs_createIndexed on jobs(createIndexed);
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.
MySQL cannot use the index for WHERE !createIndexed
, because it needs to evaluate NOT createIndexed
for each row, with a table scan.
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
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.
精彩评论