concat效率 mysql_MySQL-优化-多个group_concat&使用拥有

I've looked at similar group_concat mysql optimisation threads but none seem relevant to my issue, and my mysql knowledge is being stretched with this one.

I have been tasked with improving the speed of a script with an extremely heavy Mysql query contained within.

The query in question uses GROUP_CONCAT to create a list of colours, tags and sizes all relevant to a particular product. It then uses HAVING / FIND_IN_SET to filter these concatenated lists to find the attribute, set by the user controls and display the results.

In the example below it's looking for all products with product_tag=1, product_colour=18 and product_size=17. So this could be a blue product (colour) in medium (size) for a male (tag).

The shop_products tables contains about 3500 rows, so is not particularly large, but the below takes around 30 seconds to execute. It works OK with 1 or 2 joins, but adding in the third just kills it.

SELECT shop_products.id, shop_products.name, shop_products.default_image_id,

GROUP_CONCAT( DISTINCT shop_product_to_colours.colour_id ) AS product_colours,

GROUP_CONCAT( DISTINCT shop_products_to_tag.tag_id ) AS product_tags,

GROUP_CONCAT( DISTINCT shop_product_colour_to_sizes.tag_id ) AS product_sizes

FROM shop_products

LEFT JOIN shop_product_to_colours ON shop_products.id = shop_product_to_colours.product_id

LEFT JOIN shop_products_to_tag ON shop_products.id = shop_products_to_tag.product_id

LEFT JOIN shop_product_colour_to_sizes ON shop_products.id = shop_product_colour_to_sizes.product_id

WHERE shop_products.category_id = '50'

GROUP BY shop_products.id

HAVING((FIND_IN_SET( 1, product_tags ) >0)

AND(FIND_IN_SET( 18, product_colours ) >0)

AND(FIND_IN_SET( 17, product_sizes ) >0))

ORDER BY shop_products.name ASC

LIMIT 0 , 30

I was hoping somebody could generally advise a better way to structure this query without re-structuring the database (which isn't really an option at this point without weeks of data migration and script changes)? Or any general advise on optimisation. Using explain currently returns the below (as you can see the indexes are all over the place!).

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE shop_products ref category_id,category_id_2 category_id 2 const 3225 Using where; Using temporary; Using filesort

1 SIMPLE shop_product_to_colours ref product_id,product_id_2,product_id_3 product_id 4 candymix_db.shop_products.id 13

1 SIMPLE shop_products_to_tag ref product_id,product_id_2 product_id 4 candymix_db.shop_products.id 4

1 SIMPLE shop_product_colour_to_sizes ref product_id product_id 4 candymix_db.shop_products.id 133

解决方案

Rewrite query to use WHERE instead of HAVING. Because WHERE is applied when MySQL performs search on rows and it can use index. HAVING is applied after rows are selected to filter already selected result. HAVING by design can't use indexes.

You can do it, for example, this way:

SELECT p.id, p.name, p.default_image_id,

GROUP_CONCAT( DISTINCT pc.colour_id ) AS product_colours,

GROUP_CONCAT( DISTINCT pt.tag_id ) AS product_tags,

GROUP_CONCAT( DISTINCT ps.tag_id ) AS product_sizes

FROM shop_products p

JOIN shop_product_to_colours pc_test ON p.id = pc_test.product_id AND pc_test.colour_id = 18

JOIN shop_products_to_tag pt_test ON p.id = pt_test.product_id AND pt_test.tag_id = 1

JOIN shop_product_colour_to_sizes ps_test ON p.id = ps_test.product_id AND ps_test.tag_id = 17

JOIN shop_product_to_colours pc ON p.id = pc.product_id

JOIN shop_products_to_tag pt ON p.id = pt.product_id

JOIN shop_product_colour_to_sizes ps ON p.id = ps.product_id

WHERE p.category_id = '50'

GROUP BY p.id

ORDER BY p.name ASC

Update

We are joining each table two times.

First to check if it contains some value (condition from FIND_IN_SET).

Second join will produce data for GROUP_CONCAT to select all product values from table.

Update 2

As @Matt Raines commented, if we don't need list product values with GROUP_CONCAT, query becomes even simplier:

SELECT p.id, p.name, p.default_image_id

FROM shop_products p

JOIN shop_product_to_colours pc ON p.id = pc.product_id

JOIN shop_products_to_tag pt ON p.id = pt.product_id

JOIN shop_product_colour_to_sizes ps ON p.id = ps.product_id

WHERE p.category_id = '50'

AND (pc.colour_id = 18 AND pt.tag_id = 1 AND ps.tag_id = 17)

GROUP BY p.id

ORDER BY p.name ASC

This will select all products with three filtered attributes.