我有一个包含依赖子查询的查询,我想知道应该为哪些字段建立索引以获得最高性能?例如,下面的查询显示了姓名中包含"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, 20
EXPLAIN告诉我:
"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" ""
https://stackoverflow.com/questions/51427577
复制相似问题