StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to optimize MySQL query with "file sorting" and "using temporary" (it takes more than 10 seconds)
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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