CODEWITHSUNDEEP
mysqlperformance
Nicolas BADIA
Nicolas BADIA

Reputation: 5852

MySQL query become really slow when using ORDER BY

Here is a mysql query that take about 1ms to complete:

SELECT SQL_NO_CACHE DISTINCT invoice.*,
GROUP_CONCAT(DISTINCT line.guid) as line 
FROM invoice  
LEFT JOIN ligne ON line.invoice = invoice.guid 
GROUP BY invoice.guid 
WHERE acompte = 1
LIMIT 0, 100;

If I add ORDER BY invoice.date to the query, it become really slow and take about 3s to complete.

If I remove the LEFT JOIN (and the GROUP_CONCAT) the query take again 1ms.

I've add EXPLAIN to see what MySQL is doing when the query is slow, and I can see that it's using a temporary file:

1   SIMPLE  invoice index   NULL    PRIMARY 4   NULL    25385   Using temporary; Using filesort
1   SIMPLE  line    ref invoice invoice 5   gestixi.invoice.guid    1   Using index

I'm sure there is a way to speed up that query but I can't find it. Any idea ?

Note that I can't add an index on date (which by the way don't change anything) because I want my users to be able to sort each field of the table.

Also note that invoice.guid, line.invoice, line.guid and acompte are indexed.

EDIT

If I do a first query without the LEFT JOIN but with the ORDER BY clause to get the ids of the lines I want, and then a second query (like the one above) using these id in the WHERE clause, I can get what I need in less than 10ms.

This make me believe that it must be a way to speed up that query without adding indexes.

Upvotes: 3

Views: 364

Answers (1)

RandomSeed
RandomSeed

Reputation: 29749

I am afraid that if you must allow your users to sort on any field (and have this sort use an index) then you need an index for each possible sort. It is impossible to do otherwise by definition. Sorting on a given row may only make use of an index on this row.

I see little alternatives here. Either reduce the number of rows to be sorted (25k lines is a bit large a result set, do your users really need that many lines?) or do not allow sorts on all rows.

Notice that a query will usually not be able to use more than one index by table. As advised by others, a compound index is better for the query you mentionned, although I would rather advise the opposite order ((guid, date)) (the query first needs to select each guid, and then, for each of them, sort the corresponding rows).

Also add an index on line(guid, acompte, invoice).

(above suggestions on indexing assume MyISAM tables)

In terms of optimisation of the query itself, there is little moreto be done, considering the simple execution plan.

You might get better results with this version, or you might not:

SELECT
    invoice.*, -- DISTINCT is redudant here because of the GROUP BY clause
    GROUP_CONCAT(ligne_acompte.guid) as line  -- DISTINCT is (presumably) redundant here because guid is (presumably) unique
FROM invoice  
LEFT JOIN (
    SELECT guid, invoice
    FROM line
    WHERE acompte = 1
) AS ligne_acompte ON ligne_acompte.invoice = invoice.guid 
GROUP BY invoice.guid
ORDER BY invoice.date;

Upvotes: 1

Related Questions