Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is rewriting the query an option? –  ypercube Jun 21 at 11:56
    
@ypercube unfortunately rewriting the query is not an option. I guess I'm only able to play with sorting, without breaking out of orm. Otherwise manually rewriting would require way much work to insert the results back in. –  hinoglu Jun 21 at 12:00
    
I added a suggestion. What version of MySQL are you using exactly? It matters for this type of optimization (using index for LIMIT queries.) –  ypercube Jun 21 at 12:06
    
I'm using mariadb v 10.0.11 compatible with mysql 5.6 –  hinoglu Jun 21 at 12:09
1  
Another idea would be to keep your query but change all the INNER joins to LEFT joins. This might produce a different plan. Can you try it? –  ypercube Jun 21 at 12:13

1 Answer 1

up vote 2 down vote accepted

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 from star, follows the foreign keys, the result will be the same:

SELECT *
  FROM 
      ( SELECT * FROM star ORDER BY id DESC LIMIT 10 )                -- the only change
      AS 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 ;

Another idea would be to keep your query but change all the INNER joins to LEFT joins. This might produce a different plan that utilizes the primary key of star. 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 the id column comes from star.id or from question.id or from ...?
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.