Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.7
-
Debian 9.2, Core i7-7700k, 32G RAM
Description
It seems that optimizer fails when executing INNER JOIN select when tables are related by a foreign key.
There are 3 tables:
CREATE TABLE `default_work` ( |
`add_date` datetime(6) NOT NULL, |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`title` varchar(255) NOT NULL, |
`keywords` varchar(255) DEFAULT NULL, |
`short_text` longtext DEFAULT NULL, |
`downloads` int(10) unsigned NOT NULL, |
`published` tinyint(1) NOT NULL, |
`subject_id` int(11) NOT NULL, |
`work_type_id` int(11) NOT NULL, |
PRIMARY KEY (`id`), |
KEY `default_work_subject_id_IDX` (`subject_id`) USING BTREE, |
KEY `default_work_work_type_id_IDX` (`work_type_id`) USING BTREE, |
CONSTRAINT `default_work_FK` FOREIGN KEY (`subject_id`) REFERENCES `default_subject` (`id`), |
CONSTRAINT `default_work_FK_1` FOREIGN KEY (`work_type_id`) REFERENCES `default_worktype` (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=210673 DEFAULT CHARSET=utf8 |
|
CREATE TABLE `default_subject` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`subject` varchar(255) NOT NULL, |
`old_id` int(10) unsigned NOT NULL, |
`subject_literal` varchar(255) NOT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 |
|
CREATE TABLE `default_worktype` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`work_type` varchar(250) NOT NULL, |
`description` longtext DEFAULT NULL, |
`old_id` int(10) unsigned NOT NULL, |
`work_type_literal` varchar(250) NOT NULL, |
`title` varchar(255) NOT NULL, |
`multiple` varchar(255) NOT NULL, |
`keywords` varchar(255) NOT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `default_worktype_old_id_a8b508fe_uniq` (`old_id`), |
UNIQUE KEY `default_worktype_work_type_literal_1e609434_uniq` (`work_type_literal`) |
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |
The default_work has about 200,000 records, default_subject - 42, and default_worktype - 12
When executing this query:
SELECT `default_work`.`id`, `default_work`.`title`, `default_worktype`.`work_type`,`default_subject`.`subject` |
FROM `default_work` |
INNER JOIN `default_subject` ON (`default_work`.`subject_id` = `default_subject`.`id`) |
INNER JOIN `default_worktype` ON (`default_work`.`work_type_id` = `default_worktype`.`id`) |
ORDER BY `default_work`.`id` DESC LIMIT 100 |
I got about 9 seconds of execution time, with explain says what no primary key is used. But when I deleted foreign keys and appropriate indexes - the query runs fast and smooth. (Both explain results shown in attached images).