MySQL - Slow Query when kept a View

Question

In MySQL, I have a simple join between 2 tables. Something like

select a.id, SUM(b.qty) from a inner join b on a.id=b.id
where a.id=12345
group by a.id

It runs normal as a query. But when I keep the query

select a.id, SUM(b.qty) from a inner join b on a.id=b.id
group by a.id

in a view called view_ab, the view takes enormous amount of time when i run the following query on the view.

select * from view_ab where id = 12345

Both these tables are large tables. Unable to figure out the reason for such a drop in performance. Please help resolve this performance issue

EDIT: This is the view SQL

CREATE VIEW view_ab AS SELECT 
r.drid            AS drid,
SUM(s.return_qty) AS return_qty
FROM tbl_deliveryroute r INNER JOIN tbl_deliveryroute_sku s ON r.drid = 
s.drid GROUP BY r.drid;

This is the query

SELECT 
r.drid            AS drid,
SUM(s.return_qty) AS return_qty
FROM tbl_deliveryroute r INNER JOIN tbl_deliveryroute_sku s ON r.drid = 
s.drid WHERE r.drid=12718651
GROUP BY r.drid;

This is the query on the VIEW

SELECT * FROM view_ab WHERE drid=12718651;

Execution plan of the view

EXPLAIN EXTENDED SELECT * FROM view_ab WHERE drid=12718651;

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 PRIMARY (NULL) ref 4 const 10 100.00 (NULL)

2 DERIVED s (NULL) ALL idx_tbl_deliverroute_sku_drid (NULL) (NULL) (NULL) 15060913 100.00 USING TEMPORARY; USING filesort

2 DERIVED r (NULL) eq_ref PRIMARY,FK_tbl_deliveryroute_1 PRIMARY 4 humdemotest.s.drid 1 100.00 USING INDEX

EXPLAIN EXTENDED  SELECT 
r.drid            AS drid,
SUM(s.return_qty) AS return_qty
FROM tbl_deliveryroute r INNER JOIN tbl_deliveryroute_sku s ON r.drid = 
s.drid WHERE r.drid=12718651
GROUP BY r.drid;

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE r (NULL) const PRIMARY PRIMARY 4 const 1 100.00 USING INDEX

1 SIMPLE s (NULL) ref idx_tbl_deliverroute_sku_drid idx_tbl_deliverroute_sku_drid 4 const 22 100.00 (NULL)

Answer 1

The reason the view is slow is simple. You are executing:

SELECT *
FROM view_ab
WHERE drid = 12718651;

What you want to execute is:

select a.id, SUM(b.qty)
from a inner join
     b
     on a.id = b.id
where a.id = 12345
group by a.id;

What is actually being executed is:

select ab.*
from (select a.id, SUM(b.qty)
      from a inner join
           b
           on a.id = b.id
      group by a.id
     ) ab
where ab.id = 12345;

That is, the entire aggregation is performed first. Then the where is applied. What you want is for the predicate to be pushed up (MySQL calls this merging). You can review the documentation on this subject.

One solution would seem to be rephrasing the query as a correlated subquery:

select a.id,
       (select sum(b.qty) from b where b.id = a.id) as qty
from a 
where a.id = 12345;

Alas, subqueries in the select have the same effect, so this doesn't work.

I don't know of a solution using a view. You can avoid using views for this. The ultimate solution would be to implement a trigger to store the summarized results in another table -- effectively materializing the view.

Answer 2

From what I am seeing, you don't even need a join since you are dealing with a join on the same key column from A-B, the key already exists in table B, just query group by that. Also, I would have an index on your DeliveryRoute_SKU on its route ID column

SELECT 
      s.drid,
      sum( s.return_qty ) Return_Qty
   from
      tbl_DeliveryRoute_Sku s
   where
      s.drID = 12718651
   group by
      s.drID;

Since you are only doing the key and the sum, you don't even NEED the other table. Now if you needed other columns from the first table OTHER THAN the key, then yes, you would need the join. You could even simplify a step further since you are only querying a single key ID

SELECT 
      sum( s.return_qty ) Return_Qty
   from
      tbl_DeliveryRoute_Sku s
   where
      s.drID = 12718651;