mardi 5 mai 2015

select slow because of unused inner join

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 ?

Aucun commentaire:

Enregistrer un commentaire