2

I have this query:

SELECT article.id
FROM article
INNER JOIN article_cCountry ON article.id = ID1 AND ID2 = 1
INNER JOIN user_cCountry ON article.uId = user_cCountry.ID1 AND user_cCountry.ID2 = 1
LEFT JOIN user ON article.uId = user.ID
WHERE article.released = "TRUE"
AND article.sDate < now()
AND article.cDate != "0000-00-00 00:00:00"
AND (article.eDate > now() OR article.eDate = 0)
AND ( (user.released = true) OR (article.uId = 0) )
ORDER BY article.cDate DESC
LIMIT 0, 10

The query takes ~0.3 seconds, without the ORDER BY only ~0.001 seconds.

Any ideas why the ORDER BY is so slow?

EDIT EXPLAIN: enter image description here

EDIT 2 INDEXES enter image description here

grundig
  • 101
  • 2
  • 14
  • What does `explain select...` shows, looks like the column used in order by clause is not indexed. – Abhik Chakraborty Jul 31 '15 at 07:54
  • @AbhikChakraborty see edit – grundig Jul 31 '15 at 07:56
  • check this link http://stackoverflow.com/questions/14368211/mysql-very-slow-order-by – Birkan AYDIN Jul 31 '15 at 08:01
  • Thats correct your schema is missing some indexes I would suggest to have the indexes `article(released,sDate) and user(released)`... take a backup of the tables and see the existing indexes and if the above are not yet added add them. – Abhik Chakraborty Jul 31 '15 at 08:02
  • @AbhikChakraborty ok I've added the mentioned indexes but still have ~0.2 seconds. Is there any other possibilities to optimize? – grundig Jul 31 '15 at 08:13
  • Well the order by would happen at the 2nd after the intermediate result is returned and optimizer may choose to do the temporary file sort on the returned data to do order by and hence its useful to have an index on that column. Now to replicate the issue its really needed to see that in the DB which I cant. The overall query looks good, could you add the indexes available on tables to the question. `show indexes from table_name` – Abhik Chakraborty Jul 31 '15 at 08:23
  • Please provide `SHOW CREATE TABLE`. We can't see what indexes you have. Please qualify all fields -- for example, we can't tell which table has ID2. – Rick James Jul 31 '15 at 19:45
  • @AbhikChakraborty, I've added a screenshot above! – grundig Jul 31 '15 at 20:06

4 Answers4

3

Without ORDER BY your query will terminate after 10 rows (LIMIT).

With ORDER BY full result set needs to be generated, sorted and then first 10 rows will be returned.

vhu
  • 12,244
  • 11
  • 38
  • 48
0

Though you can never achieve the speed of outputting only 10 rows vs taking all rows and THEN sorting em and then outputting the first 10, you can do a little trick to order the recordset in the memory

SELECT id FROM (
 SELECT article.id,article.cDate
 FROM article
 INNER JOIN article_cCountry ON article.id = ID1 AND ID2 = 1
 INNER JOIN user_cCountry ON article.uId = user_cCountry.ID1 AND user_cCountry.ID2 = 1
 LEFT JOIN user ON article.uId = user.ID
 WHERE article.released = "TRUE"
 AND article.sDate < now()
 AND article.cDate != "0000-00-00 00:00:00"
 AND (article.eDate > now() OR article.eDate = 0)
 AND ( (user.released = true) OR (article.uId = 0) )
)
ORDER BY cDate DESC
LIMIT 0, 10

Hope that helps

El Gucs
  • 897
  • 9
  • 18
0
INDEX(released, cDate)

may help

Avoiding OR may help.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

Your ORDER BY column (article.cDate) should be indexed, this will stop the ORDER BY article.cDate taking a long time, as the column values will be indexed and then can be arranged exponentially faster.

Martin
  • 22,212
  • 11
  • 70
  • 132