MySql product\tag query optimisation - please help!
- by Nige
Hi There
I have an sql query i am struggling to optimise. It basically is used to pull back products for a shopping cart. The products each have tags attached using a many to many table product_tag and also i pull back a store name from a separate store table. Im using group_concat to get a list of tags for the display (this is why i have the strange groupby orderby clauses at the bottom) and i need to order by dateadded, showing the latest scheduled product first. Here is the query....
SELECT products.*, stores.name, GROUP_CONCAT(tags.taglabel ORDER BY tags.id ASC SEPARATOR " ") taglist
FROM (products)
JOIN product_tag ON products.id=product_tag.productid
JOIN tags ON tags.id=product_tag.tagid
JOIN stores ON products.cid=stores.siteid
WHERE dateadded < '2010-05-28 07:55:41'
GROUP BY products.id ASC
ORDER BY products.dateadded DESC
LIMIT 2
Unfortunately even with a small set of data (3 tags and about 12 products) the query is taking 00.0034 seconds to run. Eventually i want to have about 2000 products and 50 tagsin this system (im guessing this will be very slooooow). Here is the ExplainSql...
id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
1|SIMPLE|tags|ALL|PRIMARY|NULL|NULL|NULL|4|Using temporary; Using filesort
1|SIMPLE|product_tag|ref|tagid,productid|tagid|4|cs_final.tags.id|2|
1|SIMPLE|products|eq_ref|PRIMARY,cid|PRIMARY|4|cs_final.product_tag.productid|1|Using where
1|SIMPLE|stores|ALL|siteid|NULL|NULL|NULL|7|Using where; Using join buffer
Can anyone help?
