在MySQL中优化依赖子查询的索引
在MySQL中优化依赖子查询的索引
提问于 2018-07-20 00:20:46
我有一个包含依赖子查询的查询，我想知道应该为哪些字段建立索引以获得最高性能？例如，下面的查询显示了姓名中包含"smi“的所有订单。该查询通过orders和customer表中的shared cust#从customer表中获取客户名称。我被这个查询卡住了，因为它大多是由php开发工具自动生成的。那么，考虑到这些约束，应该索引哪些字段才能获得最佳性能呢？
SELECT *, (SELECT CONCAT(customers.last, ', ', customers.first)
FROM customers WHERE customers.cust_num = orders.cust_num) AS c_lastname
FROM orders WHERE (SELECT CONCAT(customers.last, ', ', customers.first)
FROM customers WHERE customers.cust_num = orders.cust_num) LIKE '%smi%'
ORDER BY order_id DESC LIMIT 0, 20EXPLAIN告诉我：
"1" "PRIMARY" "orders" "index" \N "PRIMARY" "4" \N "20" "Using where"
"3" "DEPENDENT SUBQUERY" "customers" "eq_ref" "PRIMARY" "PRIMARY" "4" "orders.cust_num" "1" ""
"2" "DEPENDENT SUBQUERY" "customers" "eq_ref" "PRIMARY" "PRIMARY" "4" "orders.cust_num" "1" ""回答 2
Stack Overflow用户
回答已采纳
发布于 2018-07-20 00:31:58
查询看起来会更简单，如下所示：
SELECT o.*, CONCAT(c.`last`, ', ', c.`first`) AS cName
FROM orders AS o
INNER JOIN customers AS c ON o.cust_num = c.cust_num
WHERE c.`last` LIKE '%smi%' OR c.`first` LIKE '%smi%'
ORDER BY order_id DESC
LIMIT 0, 20或者，类似于戈登的：
SELECT o.*, c.Name
FROM orders AS o
INNER JOIN (
SELECT cust_num, CONCAT(`last`, ', ', `first`) AS Name
FROM customers
WHERE `last` LIKE '%smi%' OR `first` LIKE '%smi%'
) AS c ON o.cust_num = c.cust_num
ORDER BY order_id DESC
LIMIT 0, 20;前者避免了子查询，但后者将减少对同一客户的订单的冗余连接操作；哪个性能更好可能取决于表中的实际数据。
...and回显戈登，你对“%SMI%”无能为力；MySQL不从中间开始索引字符串，因此前导通配符基本上消除了使用索引的可能性。
Stack Overflow用户
发布于 2018-07-20 00:25:44
我首先将查询重写为：
SELECT o.*, c.last_name
FROM (SELECT cust_name, CONCAT(c.last, ', ', c.first) as c_lastname
FROM customers c
HAVING c_lastname LIKE '%smi%'
) c JOIN
orders o
ON o.cust_num = c.cust_num
ORDER BY order_id DESC
LIMIT 0, 20;我不认为你可以对like做太多的事情，除非你真的是指liike 'smi%'。
但是，您可以为join在orders(cust_num)上创建一个索引。对于外部order by，您无能为力。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51427577
复制相关文章
相似问题