EverSQL Database Performance Knowledge Base
select slow because of unused inner join
Database type:
I have two tables:
CREATE TABLE `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE `B` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a_id` int(11) NOT NULL,
`c_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `IX_a_id` (`a_id`),
KEY `IX_c_id` (`c_id`),
CONSTRAINT `a_id_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
They have a couple million rows each.
explain select count(*) FROM B inner join A on B.a_id = A.id WHERE B.c_id = 7;
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
| 1 | SIMPLE | B | ref | IX_a_id,IX_c_id | IX_c_id | 4 | const | 116624 | Using where |
| 1 | SIMPLE | A | eq_ref | PRIMARY | PRIMARY | 4 | test1.B.a_id | 1 | Using index |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
Now, I can't understand why mysql is unable to ignore the un-needed inner join to A which kills performance. i.e., the following query is equivalent to the above:
select count(*) from B where B.c_id = 7
which should be easy to infer since B.a_id can't be null and B.a_id has a constraint to the unique key A.id
Is there a way to make mysql understand this ?