Databases
StackOverflow2013
Postsdbo
POMySQL query become really slow when using ORDER BY
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL query become really slow when using ORDER BY
    primarykey
    data
    text
    <p>Here is a mysql query that take about 1ms to complete:</p> <pre><code>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; </code></pre> <p>If I add <code>ORDER BY invoice.date</code> to the query, it become really slow and take about 3s to complete. </p> <p>If I remove the <code>LEFT JOIN</code> (and the <code>GROUP_CONCAT</code>) the query take again 1ms.</p> <p>I've add <code>EXPLAIN</code> to see what MySQL is doing when the query is slow, and I can see that it's using a temporary file:</p> <pre><code>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 </code></pre> <p>I'm sure there is a way to speed up that query but I can't find it. Any idea ?</p> <p><em>Note that I can't add an index on <code>date</code> (which by the way don't change anything) because I want my users to be able to sort each field of the table.</em></p> <p><em>Also note that <code>invoice.guid</code>, <code>line.invoice</code>, <code>line.guid</code> and <code>acompte</code> are indexed.</em></p> <h3>EDIT</h3> <p>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. </p> <p>This make me believe that it must be a way to speed up that query without adding indexes.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNicolas BADIA
    1. USNicolas BADIA
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL query become really slow when using ORDER BY
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL query become really slow when using ORDER BY
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL query become really slow when using ORDER BY
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTry adding index on `invoice.date` field as it will be used when you are using the order by clause. Check the explain of the order by clause with the index and check whether it is used or not..
      singulars
      1. POMySQL query become really slow when using ORDER BY
      1. USMeherzad
    2. COAs I say, I don't want to add an index on `invoice.date` because if I do so, I will have to add indexes to all the fields of my table. Anyway, I've try to add an index on it and it is not used (and doesn't change anything).
      singulars
      1. POMySQL query become really slow when using ORDER BY
      1. USNicolas BADIA
    3. COOk missed your point... Anyways give a try creating compound index on `invoice.date, invoice.guid`, if this index is not used then try explicitly telling the query optimizer to use it using `use index`
      singulars
      1. POMySQL query become really slow when using ORDER BY
      1. USMeherzad
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload