CODEWITHSUNDEEP
mysqlperformance
grundig
grundig

Reputation: 101

Slow MySQL query when using ORDER BY

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

Upvotes: 2

Views: 97

Answers (4)

Martin
Martin

Reputation: 22760

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.

Upvotes: 0

Rick James
Rick James

Reputation: 142208

INDEX(released, cDate)

may help

Avoiding OR may help.

Upvotes: 0

El Gucs
El Gucs

Reputation: 967

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

Upvotes: 0

vhu
vhu

Reputation: 12788

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.

Upvotes: 3

Related Questions