Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>Notice that a query will usually <em>not</em> 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 (<code>(guid, date)</code>) (the query first needs to select each <code>guid</code>, and then, for each of them, sort the corresponding rows).</p> <p>Also add an index on <code>line(guid, acompte, invoice)</code>.</p> <p>(above suggestions on indexing assume MyISAM tables)</p> <p>In terms of optimisation of the query itself, there is little moreto be done, considering the simple execution plan.</p> <p>You might get better results with this version, or you might not:</p> <pre><code>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; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POMySQL query become really slow when using ORDER BY
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USRandomSeed
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI just ask for the first 100 lines not for 25k. About the indexes, I'm not sure it's the issue because as I say, if I remove the LEFT JOIN (but keep the ORDER BY), the query is pretty fast. I've try your suggestion of query but unfortunately, it's a lot worse than mine. Thanks anyway for the help :-)
      singulars
      1. PO
      1. USNicolas BADIA
 

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