mysql explain 结果解释

Question

下面的查询完全符合我的预期，它很直观并且不会生成中间表。缺点是需要很长时间才能完成。

在这种情况下，我要做的是逐步分解查询并创建那些中间表和索引。这一次，我想更好地处理 explain 提供的提示，并希望得到任何指示:我在下面的查询中缺少哪些明显的优化步骤？

遵循 MySQL query optimization and EXPLAIN for a noob 中的建议我在 orders_raw 中为 order_number 、order_type 和 item 创建了索引。然而，尚不清楚这些将如何延续字符处理/正则表达式。

SELECT bundle_headers.order_number , bundle_headers.title , digital_subs.subscription_id , 1 as bundle_component
from
(
  select order_number , substring( item , 1 , 3 ) as title , quantity from orders_raw
  where order_type in (4,6)                      
) bundle_headers
inner join
(
  select order_number , subscription_id , item as title , quantity from orders_raw
  where order_type = 0 and length( item ) = 4    
) digital_subs
on bundle_headers.order_number = digital_subs.order_number and
   digital_subs.title regexp concat( '.*' , bundle_headers.title , '.*' ) and
   bundle_headers.quantity = digital_subs.quantity

UNION


SELECT bundle_headers.order_number , bundle_headers.title , print_subs.subscription_id , 1 as bundle_component
from
(
  select order_number , substring( item , 1 , 3 ) as title , quantity from orders_raw
  where order_type in (4,6)                      
) bundle_headers
inner join
(
  select order_number , subscription_id , item as title , quantity from orders_raw
  where order_type = 0 and length( item ) = 3    
) print_subs
on bundle_headers.order_number = print_subs.order_number and
   print_subs.title regexp concat( '.*' , bundle_headers.title , '.*' ) and
   bundle_headers.quantity = print_subs.quantity;

编辑，@tin tran:我还没有严格计时上面的查询和你的查询(经过一些更正后，复制在下面)在一台空闲机器上开始。我确实提交了它，但没有看到运行时间明显减少。

 SELECT bundle_headers.order_number,
   substring(bundle_headers.item,1,3) as title,
   subs.subscription_id,
   1 as bundle_component
 FROM orders_raw bundle_headers
 INNER JOIN orders_raw subs ON (bundle_headers.order_number = subs.order_number)
 WHERE (bundle_headers.order_type = 4 OR bundle_headers.order_type = 6)
   AND subs.order_type = 0
   AND bundle_headers.quantity = subs.quantity
   AND subs.item LIKE CONCAT('%',substring(bundle_headers.item,1,3),'%')
   AND (length(subs.item) = 4 OR length(subs.item) = 3)

Answer 1

请尝试这个查询，看看它是否产生相同的结果。如果它更快的话

SELECT bundle_headers.order_number,substring(bundle_headers.title,1,3) as title,subs.subscription_id,1 as bundle_component
FROM order_type bundle_headers
INNER JOIN orders_raw subs ON (bundle_headers.order_number = subs.order_number)
WHERE (bundle_headers.order_type = 4 OR bundle_headers.order_type = 6)
  AND subs.order_type = 0
  AND bundle_headers.quantity = subs.quantity
  AND subs.title LIKE CONCAT('%',substring(bundle_headers.title,1,3),'%')
  AND (length(subs.item) = 4 OR length(subs.item) = 3)