I have the following db structure:
CREATE TABLE `question` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`level_id` int(11) NOT NULL,
`video_id` int(11) NOT NULL,
`is_active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `question_b8f3f94a` (`level_id`),
KEY `question_c11471f1` (`video_id`),
CONSTRAINT `level_id_refs_id_27dd88d9` FOREIGN KEY (`level_id`) REFERENCES `level` (`id`),
CONSTRAINT `video_id_refs_id_1c4fbe15` FOREIGN KEY (`video_id`) REFERENCES `video` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3054 DEFAULT CHARSET=utf8
CREATE TABLE `level` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`success_rate` tinyint(3) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8
CREATE TABLE `video` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`file` varchar(200) DEFAULT NULL,
`name` longtext NOT NULL,
`subtitle` longtext NOT NULL,
`producer` longtext,
`director` longtext,
`details` longtext,
`related_content_url` longtext,
`counter` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3055 DEFAULT CHARSET=utf8
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(128) NOT NULL,
`last_login` datetime NOT NULL,
`is_superuser` tinyint(1) NOT NULL,
`username` varchar(82) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email` varchar(82) NOT NULL,
`is_staff` tinyint(1) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`date_joined` datetime NOT NULL,
`is_verified` tinyint(1) NOT NULL,
`verification_code` varchar(40) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`gender` varchar(1) DEFAULT NULL,
`language_id` int(11) DEFAULT NULL,
`auth_token` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `verification_code` (`verification_code`),
KEY `user_784efa61` (`language_id`),
KEY `email_idx` (`email`),
CONSTRAINT `language_id_refs_id_016597a8` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=469322 DEFAULT CHARSET=utf8
CREATE TABLE `star` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`question_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`counter` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `star_25110688` (`question_id`),
KEY `star_6340c63c` (`user_id`),
CONSTRAINT `question_id_refs_id_3c6023b7` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`),
CONSTRAINT `user_id_refs_id_4b270cea` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3737324 DEFAULT CHARSET=utf8
The orm I'm using, generates the following query for a specific page:
SELECT *
FROM `star`
INNER JOIN `question` ON (`star`.`question_id` = `question`.`id`)
INNER JOIN `level` ON (`question`.`level_id` = `level`.`id`)
INNER JOIN `video` ON (`question`.`video_id` = `video`.`id`)
INNER JOIN `user` ON (`star`.`user_id` = `user`.`id`)
ORDER BY `star`.`id` DESC
LIMIT 10;
This query runs for ages.
+------+-------------+----------+--------+---------------------------------------------+-------------------+---------+----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+---------------------------------------------+-------------------+---------+----------------------------+------+---------------------------------+
| 1 | SIMPLE | level | ALL | PRIMARY | NULL | NULL | NULL | 24 | Using temporary; Using filesort |
| 1 | SIMPLE | question | ref | PRIMARY,question_b8f3f94a,question_c11471f1 | question_b8f3f94a | 4 | level.id | 63 | |
| 1 | SIMPLE | video | eq_ref | PRIMARY | PRIMARY | 4 | question.video_id | 1 | |
| 1 | SIMPLE | star | ref | PRIMARY,star_25110688,star_6340c63c | star_25110688 | 4 | question.id | 631 | Using index condition |
| 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 | star.user_id | 1 | |
+------+-------------+----------+--------+---------------------------------------------+-------------------+---------+----------------------------+------+---------------------------------+
"order by star.id" does not use primary key. Adding use index(primary) solves the problem for me. But I don't want to write queries manually.
Is there any other way to force mysql to use primary indexes (using ordr by etc.), without hinting or forcing indexes manually?
Best Answer
Here is a suggestion for a rewrite. Perhaps your ORM can be convinced to produce this query. It's only slightly different from the original. The only change is a derived table instead of the base table
star
. As the tables have foreign keys defined and the query starting fromstar
, follows the foreign keys, the result will be the same:Another idea would be to keep your query but change all the
INNER
joins toLEFT
joins. This might produce a different plan that utilizes the primary key ofstar
. And because of the foreign keys, we are again sure that the query is equivalent and will produce the same results.Not entirely relevant to efficiency:
SELECT *
should not be used. Add only the list of the columns you need, not all the columns of all the tables involved. How are you going to identify anyway in the results whether theid
column comes fromstar.id
or fromquestion.id
or from ...?