加速sql INNER JOIN和LEFT JOIN组合语句
希望提高以下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问候
回答 3
Stack Overflow用户
发布于 2018-07-29 23:49:42
我最好的猜测是，大部分时间都花在了AND c.productTitle LIKE '%user-query-here%'上。
坏消息是，您不能在这种情况下建立普通的索引，因此数据库必须遍历所有记录并为每条记录执行子字符串匹配。
如果你想不出其他的方法(比如用=代替LIKE )，那么全文搜索就是你所需要的。好消息是MySQL有一个built-in FTS support。
Stack Overflow用户
发布于 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单元有多忙。
Stack Overflow用户
发布于 2018-08-17 15:29:07
在查看了可能的原因后，发现是Innodb buffer pool size太低。
我更改了池大小，并将like更改为MATCH() AGAINST()，并在表中重新定义了索引。
感谢您的回复。
https://stackoverflow.com/questions/51581545
复制相似问题