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

本文探讨了一个使用GROUP_CONCAT、HAVING和FIND_IN_SET进行过滤的慢速MySQL查询问题。通过将HAVING替换为WHERE,并通过条件JOIN优化查询,显著提高了查询效率。解决方案包括重新编写查询,减少不必要的聚合和使用索引来加速查询。
摘要由CSDN通过智能技术生成

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值