CODEWITHSUNDEEP
mysqlperformancesql-order-by
This is Anonymous
This is Anonymous

Reputation: 11

MySQL ORDER BY - Slowing Down Query

Question pertains to a listing site that holds listing data and lead data in two tables. When doing a query for listings and total number of leads for each listing, the results are extremely slow after adding an ORDER BY. Without the ORDER BY, the results are retrieved very fast. Any advice or help with restructuring the query below would be awesome!! Fyi, there are 20k listings and 100k leads.

SELECT ls.*, IFNULL(ld.total_leads, 0) AS total_leads
FROM listing ls 
LEFT JOIN (SELECT listing_id, COUNT(listing_id) AS total_leads 
            FROM lead GROUP BY listing_id) ld
ON (ls.listing_id = ld.listing_id)
ORDER BY ls.listing_id DESC LIMIT 0,20

EXPLAIN:

id select_type table type pos key ke ref rows Extra 
1 PRIMARY ls ALL NULL NULL NULL NULL 17215 Using temporary; Using filesort 
1 PRIMARY 2> ALL NULL NULL NULL NULL 12865 
2 DERIVED lead ALL NULL NULL NULL NULL 117830 Using temporary; Using filesort

Upvotes: 1

Views: 2107

Answers (2)

hoang nguyen
hoang nguyen

Reputation: 2209

Create index on listing_id of two table.

When you include an order by clause, the dabatase has to build a list of the rows in the correct order and then return the data in that order. This can take a lot of extra processing which translates into extra time.

It probably takes longer to sort a large number of columns, which your query might be returning. At some point you will run out of buffer space and the database will have to start swapping and perfromance will go downhill.

Try returning less columns (specify the columns you need instead of Select *) and see if the query runs faster.

Upvotes: 0

Jakob Buis
Jakob Buis

Reputation: 233

Short answer: Create an index on listing_id.

(Semi)long answer: This will speed up ordering by that column (but slow down inserts). More information: http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

Upvotes: 2

Related Questions