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