CODEWITHSUNDEEP
mysql
user2105469
user2105469

Reputation: 1433

mysql explain result interpretation

The query below does exactly what I expect it to do, is intuitive and doesn't generate intermediary tables. The downside is that it takes a long time to complete.

What I'll do in such cases is break the query in steps and create those intermediary tables & indexes. This time around I'd like to get a better handle on the hints provided by explain, and would appreciate any pointers: what obvious optimization steps am I missing in the query below?

Following the advice in MySQL query optimization and EXPLAIN for a noob I've created indices on order_number , order_type and item in orders_raw. It's unclear however how these would carry over character processing/regexes.

Output of EXPLAIN

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;

EDIT, @tin tran: I've yet to rigorously time both the query above and your query (after a couple corrections, copied below) starting out on an idle machine. I did submit it, and didn't see an obvious reduction in run time.

 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)

Upvotes: 4

Views: 86

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

please try this query see if it produces the same result. And if it's any faster 

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)

Upvotes: 1

Related Questions