I ran the following queries in MySQL -
SELECT * from table
WHERE valid is TRUE
ORDER BY priority DESC
limit 10
offset 0;
Time taken = 1 second.
vs
SELECT * from table
WHERE valid = TRUE
ORDER BY priority DESC
limit 10
offset 0;
Time taken = 66 ms.
I have indexes on (valid, priority) and (valid). Why is there such a huge difference? What is the difference between Is TRUE vs = TRUE ?
According to the Mysql Doc for the IS operator
IS boolean_value
Tests a value with a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
In SQL, a boolean_value, whether TRUE, FALSE, or UNKNOWN, is a truth value. When using the IS operator, the value being tested against must be expressed/cast as one of these truth values, and then the expression is evaluated.
In your first query:
SELECT * from table WHERE valid is TRUE ORDER BY priority DESC limit 10 offset 0;
depending on the data type of the valid column, the actual value is evaluated for each row, which would result in a full table scan, so you would see higher times.
In your second query:
SELECT * from table WHERE valid = TRUE ORDER BY priority DESC limit 10 offset 0;
when you use the = operator, you are comparing the valid column to a Boolean Literal TRUE, which is just a MySQL constant for 1.
There is a very important difference:
IS TRUE only true "true" or "false"
= TRUE can return NULL .
In particular, NULL IS TRUE returns "false".
Actually, this is not that important for IS TRUE . It is a substantial difference for IS NOT TRUE versus NOT or <> true .
That IS TRUE and IS NOT TRUE is "NULL-safe":
where NULL IS NOT TRUE --> evaluates to true and all rows are returned where NOT NULL --> evaluates to NULL and no rows are returned where NULL <> TRUE --> evaluates to NULL and no rows are returned The NULL here could be an expression that returns NULL values.
These semantics are clearly explained in the documentation .
There is a semantic difference between the two.
From documentation:
IS boolean_value
Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1
For the "=" operator, it is merely a way to equate something to compare. In your query, you are using valid to be set to True.
So, depending on your use case, you would use the operators. In your current query, they look as if they do the same thing.