Geek Answers Handbook

Mysql explains interpretation of results

The following query does exactly what I expect from it, is intuitive, and does not create staging tables. The disadvantage is that it takes a long time to complete.

What I will do in such cases is to split the query in stages and create these intermediate tables and indexes. This time I would like to get more accurate information about the hints provided by the explanation, and would appreciate any pointers: what are the obvious optimization steps that I skipped in the query below?

Following the tips for optimizing MySQL and EXPLAIN queries for noob , I created indexes on order_number, order_typeand itemin orders_raw, however, it is unclear how they carry character processing / regular expressions.

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: , ( , ), . .

 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)
+4
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)
+1

More articles:


All Articles