希望提高以下sql语句的性能,因为这需要7秒以上的时间来搜索850,000条记录。
select c.productTitle
, c.catalogue_id
, s1.catalogue_id
, s1.cpc
FROM store s1
JOIN catalogue c
ON s1.catalogue_id = c.catalogue_id
LEFT
JOIN store s2
ON (s1.catalogue_id = s2.catalogue_id AND s1.cpc < s2.cpc)
WHERE s2.cpc IS NULL
AND c.productTitle LIKE '%user-query-here%'
GROUP
BY c1.catalogue_id
我有一个目录表与850,000个产品(非重复)和一个商店表,其中包含每个产品的销售价格,商店表可以有副本,因为一个以上的卖家可以销售相同的产品。
目录表
| catalogue_id | productTitle | barcode |
| 1 | washing machine | abc |
| 2 | dish washer | def |
| 3 | toaster | ghi |
| 4 | kettle | jkl |
| 5 | mobile phone 8gb | mno |
存储表
| product_id | catalogue_id | cpc |
| 1001 | 1 | 0.01 |
| 2001 | 1 | 0.02 |
| 3001 | 2 | 0.05 |
| 4001 | 3 | 0.01 |
| 5001 | 1 | 0.05 |
预期结果...
(按catalogue_id对洗衣机进行分组,仅按每次点击的最高成本(cpc)显示产品)
washing machine | 5001 | 0.05
dish washer | 3001 | 0.05
toaster | 4001 | 0.01
问候
发布于 2018-07-29 23:49:42
我最好的猜测是,大部分时间都花在了AND c.productTitle LIKE '%user-query-here%'
上。
坏消息是,您不能在这种情况下建立普通的索引,因此数据库必须遍历所有记录并为每条记录执行子字符串匹配。
如果你想不出其他的方法(比如用=
代替LIKE
),那么全文搜索就是你所需要的。好消息是MySQL有一个built-in FTS support。
发布于 2018-07-30 01:22:10
那么,您应该重新定义您的SQL。第一个连接可能是内连接,请使用它来明确意图。获取嵌套的查询。
select c.productTitle
, c.catalogue_id
, s1.catalogue_id
, s1.cpc
FROM store s1
JOIN catalogue c
ON s1.catalogue_id = c.catalogue_id
LEFT
JOIN store s2
ON (s1.catalogue_id = s2.catalogue_id AND s1.cpc < s2.cpc)
WHERE s2.cpc IS NULL
进入一个查询,其余的在外部,但也要考虑避免IS NULL语句和have for那个空代码。
另一个问题是查询中表的排序。首先选择最小的表,或者从连接中获得最小乘积的表,然后再执行其余的操作。
下一个问题是表上有什么样的索引。如果查询耗时超过7秒,你应该尝试一下哪种方法效果最好。
还有一个问题是,如果这不是重新设计应该起作用的问题,那么I/O单元有多忙。
发布于 2018-08-17 15:29:07
在查看了可能的原因后,发现是Innodb buffer pool size
太低。
我更改了池大小,并将like
更改为MATCH() AGAINST()
,并在表中重新定义了索引。
感谢您的回复。
https://stackoverflow.com/questions/51581545
复制相似问题