CODEWITHSUNDEEP
mysqlsqldatabase
jeremy
jeremy

Reputation: 201

Mysql ORDER BY and LIMIT slow query

I have the following query which is taking about 20 seconds on records of 60,000 in the sale table. I understand that the ORDER BY and LIMIT are causing the issue, as when ORDER BY is removed it is returned in 0.10 seconds.

I am unsure how to optimise this query, any ideas?

The explain output is here https://gist.github.com/anonymous/1b92fa64261559de32da

SELECT sale.saleID as id, 
node.title as location, 
sale.saleTotal as total, 
sale.saleStatus as status, 
payment.paymentMethod, 
field_data_field_band_name.field_band_name_value as band, 
invoice.invoiceID, 
field_data_field_first_name.field_first_name_value as firstName, 
field_data_field_last_name.field_last_name_value as lastName, 
sale.created as date

FROM sale                  
LEFT JOIN payment
ON payment.saleID = sale.saleID
LEFT JOIN field_data_field_location
ON field_data_field_location.entity_id = sale.registerSessionID
LEFT JOIN node
ON node.nid = field_data_field_location.field_location_target_id    
LEFT JOIN invoice
ON invoice.saleID = sale.saleID                         
LEFT JOIN profile
ON profile.uid = sale.clientID                     
LEFT JOIN field_data_field_band_name
ON field_data_field_band_name.entity_id = profile.pid
LEFT JOIN field_data_field_first_name
ON field_data_field_first_name.entity_id = profile.pid
LEFT JOIN field_data_field_last_name
ON field_data_field_last_name.entity_id = profile.pid
ORDER BY sale.created DESC
LIMIT 0,50

Upvotes: 3

Views: 640

Answers (3)

Joop Eggen
Joop Eggen

Reputation: 109547

Indices first. But another technique concerning LIMIT, used by paging for instance, is to use values: last row of page yielding start value for search for next page.

As you use ORDER BY sale.created DESC you could guess a sufficiently large period:

WHERE sale.created > CURRENT_DATE() - INTERVAL 2 MONTH

An index on created a must.

Upvotes: 0

user5217717
user5217717

Reputation:

Minimal:

ALTER TABLE `sale` ADD INDEX (`saleID` ,  `created`);
ALTER TABLE `invoice` ADD INDEX (`saleID`);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Possibly, you cannot do anything. For instance, when you are measuring performance, are you looking at the time to return the first record or the entire results set? Without the order by, the query can return the first row quite quickly, but you still might need to wait a bit to get all the rows.

Assuming the comparison is valid, the following index might help: sale(created, saleId, clientId, SaleTotal, SaleStatus. This is a covering index for the query, carefully constructed so it can be read in the right order. If this avoids the final sort, then it should speed the query, even for fetching the first row.

Upvotes: 3

Related Questions