开发者

MySql product\tag query optimisation

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 5开发者_如何学C0 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?


IMO, you do not need to worry.

  • Having 2000 products would NOT take 100 times longer than 20 products. That's what indexes are for (and databases, really...).
  • 2000 records is still a very small table for MySQL.

I think you can leave the query as is and you'll soon find out that it runs fine.

Only once the result set gets really big (bigger than the system's memory - e.g. several GBs), the sorting may take a while since the DB will use the disk, this is when you can start worrying. In this case it is sometimes useful to create a temporary table for the unsorted results, add an index to it, and them select from it with ORDER BY.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜