Affects Version/s: 5.5.32
Fix Version/s: 5.5.33
This is a copy of this MySQL bug: http://bugs.mysql.com/bug.php?id=69359
I am filing it here in hopes it will get more attention than from the MySQL folks.
Note, this bug occurs in BOTH MySQL and MariaDB.
Restatement of bug follows...
for a description. Quoting that here:
I have two tables Person and Message and the latter has a foreign key to the former. Each table has id as the primary key column, and the Person table also has a column personId which is (uniquely) indexed.
The query below should take advantage of the personId key index, but instead MySQL requires scanning the entire Message table for some reason:
But when I comment out the 'M002649397' IS NULL OR clause (which has no effect on the result), the query suddenly gets more efficient:
The bug is that the 'M002649397' IS NULL expression, which is always false, is not being optimized away.
Here is a schema to test with: