CODEWITHSUNDEEP
mysqlindexingquery-optimization
frgtv10
frgtv10

Reputation: 5450

Order by slows down query

I have the following query:

select 
    `gasr`.`RID`,
    `gasr`.`ID`,
    `gr`.`RID`,
    `gr`.`TID`
    ...
from
    (((((((((((( `gasr`
    left join `gasro` ON ((`gasr`.`ID` = `gasro`.`ARID`)))
    left join  `gro` ON ((`gro`.`RID` = `gasr`.`RID`)))
    left join  `p` ON ((`p`.`ID` = `gasr`.`ID`)))
    left join  `l` ON ((`l`.`ID` = `p`.`LID`)))
    left join `k` ON ((`k`.`ID` = `p`.`KID`)))
    left join  `s` ON ((`s`.`ID` = `p`.`TID`)))
    left join  `ad` ON ((`ad`.`ID` = `p`.`DID`)))
    left join  `ae` ON ((`ae`.`ID` = `p`.`EID`)))
    left join  `gr` ON ((`gasr`.`RID` = `gr`.`ID`)))
    left join  `gs` ON ((`gs`.`ID` = `gr`.`SID`)))
    left join `ka` ON ((`ka`.`ID` = `gs`.`KID`)))
    left join `m` ON ((`m`.`ID` = `ka`.`MID`))) 
ORDER BY gs.ID, gr.RID

Which does take some time (about 5 seconds) when using the ORDER BY as described. If I will not use ORDER BY its really fast (0.08 seconds).

Explain shows me that there will be an temporary table created: enter image description here

On both fields of the order by part is an normal index (asc).

Is this the problem? How can i avoid this?

TIA Matt

Upvotes: 0

Views: 203

Answers (1)

vollie
vollie

Reputation: 1015

frgtv10, I want to say one word to you, just one word: filesort

"The Graduate" quotes aside, the fact that you've got keys on both the fields you're sorting by doesn't mean MySQL will be able to use them for sort, and, in this case, it isn't. To quote MySQL ref's ORDER BY Optimization:

In some cases, MySQL cannot use indexes to resolve the ORDER BY,…. These cases include the following:

…

  • You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

Now, whether or not this is a true problem depends on the application of your query, specifically whether this specific query is allowed to be slow or not.

Avoiding the issue by getting this SELECT + ORDER combo up to speed is going to give you a major headache, if it is even possible at all. Personally, in this case, i'd go for sorting the data after retrieval if such a thing is possible. Then again, I would recommend against using more than triple or maybe quadruple JOINs in a single query (a quick search will show you that many already hit this issue on a single JOIN) and would thus look for a solution that avoids this duodecuple join in its entirety.

Upvotes: 1

Related Questions