What is difference between `flag is TRUE` vs `flag = TRUE` in MySQL?

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 ?

Answer

As per the Mysql Doc for IS operator

IS boolean_value

Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

In SQL, a boolean_value – either TRUE , FALSE or UNKNOWN – is a truth value. When using IS operator, the value you are testing 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 datatype of the valid column, the truth value is evaluated for each row which would result in a full table scan, hence 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 = operator, you are comparing the valid column to Boolean Literal TRUE, which is just a MySQL constant for 1.