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.

EXPLAIN states 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?