MySql product\tag query optimisation - please help!
Posted
by Nige
on Stack Overflow
See other posts from Stack Overflow
or by Nige
Published on 2010-05-28T12:20:57Z
Indexed on
2010/05/28
12:21 UTC
Read the original article
Hit count: 184
mysql
|query-optimization
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?
© Stack Overflow or respective owner