What's the difference between !col and col=false in MySQL?
Posted
by Mask
on Stack Overflow
See other posts from Stack Overflow
or by Mask
Published on 2010-03-25T16:08:55Z
Indexed on
2010/03/25
16:23 UTC
Read the original article
Hit count: 438
Filed under:
mysql
|query-optimization
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);
© Stack Overflow or respective owner
