Databases
StackOverflow2013
Postsdbo
POHow to optimize MySQL query with "file sorting" and "using temporary" (it takes more than 10 seconds)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimize MySQL query with "file sorting" and "using temporary" (it takes more than 10 seconds)
    primarykey
    data
    text
    <p>I know there's a thousand similar questions, and I read a lot of them, but I don't know how to improve the performance of the following query:</p> <pre><code>SELECT DATE_FORMAT(c.date, "%d/%m/%Y") AS ddmmyy, c.idQualification, q.name, SUM(idContactList = "") + COUNT( DISTINCT NULLIF( CONCAT( c.idContactList, c.idActivity ), "" ) ) AS numCalls FROM callsBreakdown c LEFT JOIN qualification q ON q.idQualification = c.idQualification LEFT JOIN activityCampaign a ON (a.idActivity = c.idActivity) LEFT JOIN qualificationCampaign qc ON (qc.idCampaign = a.idCampaign AND qc.idQualification = c.idQualification) WHERE c.date BETWEEN "2011-01-01" AND "2012-10-01" GROUP BY c.date, q.name </code></pre> <p>If the date range is wide, like 1 year or more, the query takes 10-12 seconds to finish. In the EXPLAIN about callsBreakdown table (the others have "using index" in Extra column and they're quick):</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c ALL date NULL NULL NULL 379553 Using where; Using temporary; Using filesort </code></pre> <p>The server doesn't use date index and need temporary table and filesort. Any idea? Thanks!</p> <p><em>Edit: I change the alias "date" to "ddmmyy" and the result is the same. Alias is not the problem.</em></p> <p><em>Edit 2: If the date range is two months or less, MySQL use date index (key) instead "NULL" key</em></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USStokres
    1. USStokres
    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. This table or related slice is empty.
    1. COAn index is not guaranteed to be used. MySQL has a cost based optimizer. If it considers that the cost of using the index is greater than the cost of not using it, then it won't use it.
      singulars
      1. POHow to optimize MySQL query with "file sorting" and "using temporary" (it takes more than 10 seconds)
      1. USMark Byers
 

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