I have got this question:
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 requires > 1 sec when
classifieds consists of 168k rows, that is too lengthy.
FORCE INDEX (advertiser_id) permitted me to create it lower to .00x secs with no
ORDER BY clause. The
timestamp column is indexed, too, and that i attempted adding
FORCE INDEX (timestamp), however it did not help.
Using where; Using temporary; Using filesort around the first
SELECT in the
classifieds table - which clearly causes the performance problem.
Are you able to assist me on that one?
Thanks ahead of time!
PS: The objective of this question is to buy the five latest classifieds (including some more information for example picture, category, zipcode and area title). In addition, just one classified ought to be proven per marketer. Is this so difficult?
PPS: I attempted to pin the issue lower whenever possible and wound up with this particular query:
SELECT cl.title FROM classifieds cl GROUP BY cl.advertiser_id ORDER BY cl.timestamp DESC LIMIT 5
It requires incredible 23 secs! With
FORCE INDEX (advertiser_id) I'm able to go to at least one sec. Basically remove either the audience BY or even the ORDER BY, it is going lower to .0003 secs.
Something's gotta be wrong with my tables/indexes? I ought to not require
FORCE INDEX (btw:
USE INDEX does not work - I have to pressure it!) also it should not take that lengthy!
I do not think there's in whatever way of staying away from the truth that sorting 168k rows normally takes some doing, no matter indexing. It's one factor to discover rows inside your table by a catalog, but when it's found them the engine still needs to sort them.
1s appears pretty reasonable in my experience btw.
(Removed edit recommending alternative indexes OP attempted this without results)
Although slightly updated, I'd consider searching at the where clause in your classifieds table and find out in the event that has any indexes to make use of... For example by confirmed, cost, country. Whichever can be obtained using the cheapest probable record counts I'd list first -- most likely put country first, then confirmed. Addiitonally, take away the group by. You've got no aggregation functions connected towards the query.
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
By altering to fit your qualification, I have move that for an inner pre-query that will get per marketer that qualifies the factors, will get the utmost timestamp entry orders newest and limits to five because the first "table" being queried for that result set. From that, I've 5 records to become listed on towards the other tables that ought to be almost immediate when you are experiencing.
Perhaps you have attempted upgrading the data up for grabs?