Geek Answers Handbook

Slow SQL query due to ORDER BY not using index

I have this query:

SELECT cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cs.name AS cat_name, pix.file_name, area.area_name FROM classifieds cl FORCE INDEX (advertiser_id) INNER JOIN classifieds_pix pix ON cl.ID = pix.classified_id INNER JOIN cat_names_sub cs ON cl.cat_id = cs.ID INNER JOIN zip_codes zip ON cl.zip_id = zip.zip_id INNER JOIN area_names area ON zip.area_id = area.id WHERE cl.confirmed = 1 AND cl.price != '' AND cl.country = 'de' GROUP BY cl.advertiser_id ORDER BY cl.timestamp DESC LIMIT 5

It takes> 1 s when classifieds contains 168k lines that are too long. FORCE INDEX (advertiser_id) allowed me to reduce to 0.00x sec without an ORDER BY . The timestamp column is also indexed, and I tried adding FORCE INDEX (timestamp) , but that didn't help.

EXPLAIN says Using where; Using temporary; Using filesort Using where; Using temporary; Using filesort Using where; Using temporary; Using filesort about the first SELECT from the classifieds table, which obviously causes performance issues.

Can you help me on this?

Thanks in advance!

PS: The purpose of this request is to get the last 5 ads (including some additional information such as image, category, zip code and area name). In addition, only one section should be displayed per advertiser. Can it be that hard?

PPS: I tried to attach the problem as much as possible to the end and received this request:

 SELECT cl.title FROM classifieds cl GROUP BY cl.advertiser_id ORDER BY cl.timestamp DESC LIMIT 5

It takes an incredible 23 seconds! With FORCE INDEX (advertiser_id) I can take it up to 1 second. If I delete either GROUP BY or ORDER BY, it will drop to 0.0003 seconds.

Is something supposed to be wrong with my tables / indexes? I do not need FORCE INDEX (btw: USE INDEX does not work - I need to force it!), And it should not last so long!

+4
source share
5 answers

I don’t think there is a way to avoid the fact that sorting 168k strings will do some things regardless of indexing. It’s one thing to find the rows in the table by index, but as soon as they find them, the engine still needs to sort them.

1s seems pretty reasonable for me by the way.

(Removed editing suggesting alternative indexes; OP tried this without success)

+3
source

Despite a slight restructuring, I would consider looking at your where clause on your ad table and see if it has any indexes to use ... For example, confirmed, price, country. Whichever option is available with the smallest possible number of entries that I would name first, I probably put the country first and then confirmed. Also, delete the group. You do not have aggregation functions associated with the request.

 SELECT STRAIGHT_JOIN cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cs.name AS cat_name, pix.file_name, area.area_name FROM ( select clMax.advertiser_id, max( clMax.TimeStamp ) as AdvMaxTime from findix.classifieds clMax where clMax.confirmed = 1 AND clMax.price != '' AND clMax.country = 'de' group by 1 order by 2 desc limit 5 ) clQualified, findix.classifieds cl, findix.classifieds_pix pix, findix.cat_names_sub cs, findix.zip_codes zip, findix.area_names area WHERE clQualified.advertiser_id = cl.advertiser_id AND clQualified.AdvMaxTime = cl.timestamp; AND cl.ID = pix.classified_id AND cl.cat_id = cs.ID AND cl.zip_id = zip.zip_id AND zip.area_id = area.id

Changing according to your qualifications, I turn to the internal preliminary request, which is received for each advertiser who qualifies the criteria, receives maximum orders for entering timestamps, and the latter are limited to 5 when the first "table" is requested for a set of results. From this, I have 5 entries to join other tables, which should be almost instantaneous when you encounter.

+2
source

Have you tried multiple indexes?

Like this:

 CREATE INDEX adv_tt ON classifieds ( advertiser_id , `timestamp` );

Or even this:

 CREATE INDEX adv_tt ON classifieds ( confirmed , price , country , advertiser_id , `timestamp` );

PS: I don’t know if MySQL applied GROUP BY or ORDER BY first, if ORDER BY first, you have to reorder the columns in INDEX (... timestamp , advertiser_id)

+1
source

Have you tried updating statistics in a table?

0
source

Your request F * ed up. You have GROUP BY cl.advertiser_id , but also ORDER BY cl.timestamp DESC timestamp is not in Group BY This should not be allowed!

Why do you have Group BY at all !! Pull it out. Remove the force index. Then correct your code so that it does not have any duplicates for which the group is not used.

Edit: Try

 SELECT cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cl.timestamp cs.name AS cat_name, pix.file_name, area.area_name FROM findix.classifieds cl INNER JOIN findix.classifieds_pix pix ON cl.ID = pix.classified_id INNER JOIN findix.cat_names_sub cs ON cl.cat_id = cs.ID INNER JOIN findix.zip_codes zip ON cl.zip_id = zip.zip_id INNER JOIN findix.area_names area ON zip.area_id = area.id WHERE cl.confirmed = 1 AND cl.price != '' AND cl.country = 'de' ORDER BY cl.timestamp DESC

or

 SELECT cl.advertiser_id,cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cl.timestamp max(cs.name) AS cat_name, max(pix.file_name) as file_name, max(area.area_name) as area.area_name FROM findix.classifieds cl INNER JOIN findix.classifieds_pix pix ON cl.ID = pix.classified_id INNER JOIN findix.cat_names_sub cs ON cl.cat_id = cs.ID INNER JOIN findix.zip_codes zip ON cl.zip_id = zip.zip_id INNER JOIN findix.area_names area ON zip.area_id = area.id WHERE cl.confirmed = 1 AND cl.price != '' AND cl.country = 'de' Group By cl.advertiser_id,cl.title, cl.URL, cl.ID AS ad_id, cl.cat_id, cl.price, cl.timestamp ORDER BY cl.timestamp DESC
0
source

More articles:


All Articles