MySQL index optimization using Subquery vs Left Joins

Question

I created 2 queries that I can use that perform the same function. They both contain properties that I would like to combine into a single request, but I could not.

QUERY 1 - Gives me exactly the results that I want. Slow (~ 0.7 sec)

QUERY 2 - Gives me a lot of lines that I ignore and skip. Fast (~ 0.005 sec)

My goal is to modify QUERY 2 to remove all zero price lines except 1 for each item. It seems that I can’t do this without rushing to the play. This is due to my lack of experience and understanding of using index in MySQL.

QUERY 1

Uses a poorly designed subquery that does not allow indexing via tbl_sale (e), which contains 10k lines.

SELECT b.id, b.sv, b.description, der.store_id, f.name, der.price FROM tbl_watch AS a LEFT JOIN tbl_item AS b ON a.item_id = b.id LEFT JOIN ( SELECT c.store_id, d.flyer_id, e.item_id, e.price FROM tbl_storewatch AS c, tbl_storeflyer AS d FORCE INDEX ( storebeg_ndx ) , tbl_sale AS e WHERE c.user_id = '$user_id' AND ( d.store_id = c.store_id AND d.date_beg = '20121206' ) AND e.flyer_id = d.flyer_id ) AS der ON a.item_id = der.item_id LEFT JOIN tbl_store as f ON der.store_id = f.id WHERE a.user_id = '$user_id' ORDER BY b.description ASC 

Here is an EXPLAIN example for QUERY 1

 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ref user_item_ndx user_item_ndx 4 const 30 Using index; Using temporary; Using filesort 1 PRIMARY b eq_ref PRIMARY PRIMARY 4 a.item_id 1 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 300 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 der.store_id 1 2 DERIVED c ref user_ndx user_ndx 4 6 2 DERIVED e ALL NULL NULL NULL NULL 9473 Using join buffer 2 DERIVED d eq_ref storebeg_ndx storebeg_ndx 8 c.store_id 1 Using where 

QUERY 2

Uses all left joins that are very effective (except for ORDER BY). Indexes are used for each connection. This query returns all possible matches for each element in tbl_watch. Here is the request:

 SELECT b.id, b.sv, b.description, c.store_id, f.name, e.price FROM tbl_watch AS a LEFT JOIN tbl_item AS b ON a.item_id = b.id LEFT JOIN tbl_storewatch AS c ON c.user_id = '$user_id' LEFT JOIN tbl_storeflyer AS d ON d.store_id = c.store_id AND d.date_beg = '$s_date' LEFT JOIN tbl_sale AS e ON e.item_id = a.item_id AND e.flyer_id = d.flyer_id LEFT JOIN tbl_store as f ON d.store_id = f.id WHERE a.user_id = '$user_id' ORDER BY b.description ASC 

Here is an EXPLAIN example for a query:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a ref user_item_ndx user_item_ndx 4 const 6 Using index; Using temporary; Using filesort 1 SIMPLE b eq_ref PRIMARY PRIMARY 4 a.item_id 1 1 SIMPLE c ref user_ndx user_ndx 4 const 2 1 SIMPLE d eq_ref storebeg_ndx,storendx storebeg_ndx 8 c.store_id,const 1 1 SIMPLE e eq_ref itemflyer_ndx itemflyer_ndx 8 a.item_id,d.flyer_id 1 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 d.store_id 1 

How can I modify QUERY 2 (more efficiently) to give me only the rows I need in QUERY 1 to work with?

Thanks Mike

Answer 1

I think this query will give you what you want:

 select a.id, a.sv, a.description, c.id, c.name, b.price from tbl_item a left outer join tbl_sale b on (a.id=b.item_id) left outer join tbl_storeflyer d on (b.flyer_id=d.flyer_id and d.date_beg = '20120801') left outer join tbl_store c on (d.store_id = c.id) left outer join tbl_storewatch x on (c.id = x.store_id) left outer join tbl_watch y on (a.id = y.item_id); 

involving NULL, you are likely to have several left joins. An alternative way is to use a join that can be faster with MySQL:

  select a.id, a.sv, a.description, c.id as store_id, c.name, b.price from tbl_item a, tbl_sale b, tbl_storeflyer d, tbl_store c, tbl_storewatch x, tbl_watch y where a.id = b.item_id and b.flyer_id = d.flyer_id and d.store_id = c.id and c.id = x.store_id and a.id = y.item_id and d.date_beg = '20120801' union select a.id, a.sv, a.description, null as store_id, null as name, null as price from tbl_item a where a.id not in (select b.item_id from tbl_sale b); 

you can play with the second half of the join, which is the left outer join, and not the "not in" subquery - it depends on how your version of MySQL is optimized.

Answer 2

Your subquery in QUERY 1 uses implicit inner joins, while query 2 uses all left joins with an explicit join. So there is no suggestion to exclude data in query 2. I would take LEFT in a couple of lines (as noted) and see how this improves things:

 SELECT b.id, b.sv, b.description, c.store_id, f.name, e.price FROM tbl_watch AS a LEFT JOIN tbl_item AS b ON a.item_id = b.id LEFT JOIN tbl_storewatch AS c ON c.user_id = '$user_id' -- Left removed below JOIN tbl_storeflyer AS d ON d.store_id = c.store_id AND d.date_beg = '$s_date' -- Left removed below JOIN tbl_sale AS e ON e.item_id = a.item_id AND e.flyer_id = d.flyer_id LEFT JOIN tbl_store as f ON d.store_id = f.id WHERE a.user_id = '$user_id' ORDER BY b.description ASC` 

You can also think about accepting and proposing from associations and moving them to WHERE:

 SELECT b.id, b.sv, b.description, c.store_id, f.name, e.price FROM tbl_watch AS a LEFT JOIN tbl_item AS b ON a.item_id = b.id LEFT JOIN tbl_storewatch AS c ON c.user_id = '$user_id' JOIN tbl_storeflyer AS d ON d.store_id = c.store_id JOIN tbl_sale AS e ON e.item_id = a.item_id LEFT JOIN tbl_store as f ON d.store_id = f.id WHERE a.user_id = '$user_id' AND d.date_beg = '$s_date' AND e.flyer_id = d.flyer_id ORDER BY b.description ASC 

and finally, the math of the date is pretty intense. In query 2, using external joins, you avoid a lot, but you may need to. I will try to use a subquery to get the identifiers and limit it to this:

 SELECT b.id, b.sv, b.description, c.store_id, f.name, e.price FROM tbl_watch AS a LEFT JOIN tbl_item AS b ON a.item_id = b.id LEFT JOIN tbl_storewatch AS c ON c.user_id = '$user_id' JOIN tbl_storeflyer AS d ON d.store_id = c.store_id JOIN tbl_sale AS e ON e.item_id = a.item_id LEFT JOIN tbl_store as f ON d.store_id = f.id WHERE a.user_id = '$user_id' AND e.flyer_id = d.flyer_id AND d.id in (select d.id from d where date_beg = '$s_date') ORDER BY b.description ASC