I am wondering which is faster?
SELECT * FROM `table` WHERE `is_deleted` = false;
or
SELECT * FROM `table` WHERE NOT `is_deleted`
Thank you
-
Don't know for certain, but my guess is that the query optimizer would treat the two queries as equivalent.aroth– aroth2015-12-08 05:45:25 +00:00Commented Dec 8, 2015 at 5:45
-
1Is the second query actually valid?Eduardo Escobar– Eduardo Escobar2015-12-08 05:46:31 +00:00Commented Dec 8, 2015 at 5:46
-
yes the second query is valid.zer09– zer092015-12-08 05:51:53 +00:00Commented Dec 8, 2015 at 5:51
Add a comment
|
2 Answers
Schema
create table t123
(
id int auto_increment primary key,
x boolean not null,
key(x)
);
truncate table t123;
insert t123(x) values (false),(true),(false),(true),(false),(true),(false),(true),(false),(true),(false),(true);
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
insert t123(x) select (x) from t123;
select count(*) as rowCount from t123;
+----------+
| rowCount |
+----------+
| 3145728 |
+----------+
We now have 3.1M rows.
A
explain SELECT * FROM t123 WHERE x=false;
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
| 1 | SIMPLE | t123 | ref | x | x | 1 | const | 1570707 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+
B
explain SELECT * FROM t123 WHERE NOT `x`;
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | t123 | index | NULL | x | 1 | NULL | 3141414 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
So A is faster, because it is able to use the native datatype (as seen in an index that has it), and does not force a table scan due to the way B deals with the data conversion (and does cause a table scan)
The proof of it is in the explain output, with the number of rows required to determine the answer, and the lack of a use of an index (the ref column) even on the column for both queries.
Mysql manual page for Explain Syntax.
Comments
SELECT * FROM table WHERE NOT is_deleted
This query will give you faster and appropriate result.
Because in Mysql better to use Not operator for boolean data types.
1 Comment
zer09
sorry to resurrect this, but do you have any proof about this? based on the answer above and my test, it will result a full table scan, and that is not good. So how you prove that it is faster, and what do you mean
appropriate result?