Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.19
-
Ubuntu 14.04 LTS
Description
For a query like:
SELECT |
`a`.`id`,
|
`a`.`person`
|
FROM
|
`parts` AS `a` |
LEFT JOIN |
`persons` AS `person` ON `a`.`person` = `person`.`id` |
LEFT JOIN |
`bosses` AS `boss` ON `boss`.`id` = `person`.`boss` |
LEFT JOIN |
`logins` AS `login` ON `login`.`id` = `boss`.`login` |
WHERE
|
(`login`.`type` = '2') |
ORDER BY `a`.`id` DESC |
LIMIT 30;
|
The query optimizer returns:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | login | ref | PRIMARY,type_KEY | type_KEY | 47 | const | 23 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | boss | ref | PRIMARY,login_KEY | login_KEY | 47 | login.id | 1 | Using index |
| 1 | SIMPLE | person | ref | PRIMARY,boss_KEY | boss_KEY | 47 | boss.id | 1 | Using index |
| 1 | SIMPLE | a | ref | person_KEY | person_KEY | 47 | person.id | 6474 | Â |
The query takes a couple of seconds to complete. If I just add an index hint to PRIMARY on the parts table (alias `a`), the query takes 0.00 sec to complete. Every attribute used in the example is indexed.