SELECT download_log.code, patterns.title, tier_3.name, tier_2.name, tier_1.name, Count( download_log.code ) AS qty
FROM download_log LEFT JOIN patterns ON patterns.code = download_log.code) LEFT JOIN patterns__categories ON patterns__categories.pattern_id = patterns.pattern_id LEFT JOIN categories AS tier_3 ON patterns__categories.cat_id = tier_3.cat_id LEFT JOIN categories AS tier_2 ON tier_3.parent_id = tier_2.cat_id LEFT JOIN categories AS tier_1 ON tier_2.parent_id = tier_1.cat_id WHERE (
'2009-02-07 00:00:00' < download_log.stamp
GROUP BY download_log.code;
Try above query it will be much faster; but this will return only those products that are downloaded in last 90 days. If no download exist for a product it will not appear in results. You can run a separate query to get those which are not in these results.
Hi - Did you check the code before your reply? Yes, performance is normally all about having indexes (and persuading Mysql to utilise those indexes!). Yet the two different statements both seem to specify the joins using the same fields in the same way for example:
WHERE '2009-02-07 00:00:00' < download_log.stamp
So my question remains - why is the new code more efficient than the first? I may be missing something in the syntax but the only difference appears to be avoiding the use of an Inline view - the fields are joined without use of functions in both queries. Would the use of the syntax "LEFT JOIN" instead of "INNER JOIN" explain it?