Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.32
-
None
-
None
-
openSUSE 12.1
mariadb-5.5.28a-1.4.1.x86_64
Description
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...
See
http://stackoverflow.com/questions/16848190/mysql-why-isnt-foo-is-null-optimized-away
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:
mysql> EXPLAIN SELECT `m`.*
|
-> FROM
|
-> `Message` AS `m`
|
-> LEFT JOIN
|
-> `Person` AS `p` ON (`m`.`person` = `p`.`id`)
|
-> WHERE
|
-> 'M002649397' IS NULL OR
|
-> `p`.`personId` = 'M002649397';
|
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
|
| 1 | SIMPLE | m | ALL | NULL | NULL | NULL | NULL | 273220 | |
|
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 8 | pcom.m.person | 1 | Using where |
|
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
|
2 rows in set (0.00 sec)
|
But when I comment out the 'M002649397' IS NULL OR clause (which has no effect on the result), the query suddenly gets more efficient:
mysql> EXPLAIN SELECT `m`.*
|
-> FROM
|
-> `Message` AS `m`
|
-> LEFT JOIN
|
-> `Person` AS `p` ON (`m`.`person` = `p`.`id`)
|
-> WHERE
|
-> -- 'M002649397' IS NULL OR
|
-> `p`.`personId` = 'M002649397';
|
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
|
| 1 | SIMPLE | p | const | PRIMARY,personId | personId | 767 | const | 1 | Using index |
|
| 1 | SIMPLE | m | ref | FK9C2397E7A0F6ED11 | FK9C2397E7A0F6ED11 | 9 | const | 3 | Using where |
|
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
|
2 rows in set (0.01 sec)
|
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:
create table Message (
|
type char(1) not null,
|
id bigint not null auto_increment,
|
createTime datetime not null,
|
updateTime datetime not null,
|
person bigint,
|
primary key (id)
|
) ENGINE=InnoDB default charset=utf8 collate=utf8_bin;
|
|
|
create table Person (
|
id bigint not null auto_increment,
|
createTime datetime not null,
|
updateTime datetime not null,
|
firstName varchar(255),
|
lastName varchar(255),
|
middleName varchar(255),
|
personId varchar(255) not null unique,
|
primary key (id)
|
) ENGINE=InnoDB default charset=utf8 collate=utf8_bin;
|
|
|
create index idx_Message_createTime on Message (createTime);
|
|
|
alter table Message
|
add index FK9C2397E7A0F6ED11 (person),
|
add constraint FK9C2397E7A0F6ED11
|
foreign key (person)
|
references Person (id);
|
|
|
create index idx_Person_lastName on Person (lastName);
|
Attachments
Issue Links
Activity
The fix was pushed into 5.5, and will be available in MariaDB 5.5.33.
Archie, thanks for reporting this bug here. The fix should be an improvement for the MariaDB optimizer.
Maybe, we could remove items that have
item->const_item() && !item->is_expensive() && item->val_int() == 0
from having item->not_null_tables() put into not_null_tables_cache.