MySQL - Slow Query when kept a View

1
votes

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)

2
mysqlsql
Please examine the execution plans of both queries by prepending EXPLAIN to each of them. Note that for the former MySQL may be doing filtering first then grouping, and for the latter vice-versa which kind of explains the issue. (If this is the case, you could stick to a stored procedure instead.) – minmaxavg
Thank you for the response. The PLAN for each of these statements is different. Also, we have recently setup a staging environment of our production environment. This view is used extensively. Moving to a stored procedure means much work – Rajesh Sharma
The same view works well in the production environment. The issue with the view is experienced in the staging environment only. – Rajesh Sharma
Posting an EXPLAIN EXTENDED result of the query (possibly censoring sensitive information) would be helpful. Also, it might be worth checking if both schemas for staging and production environment are identical, especially the KEY / INDEX part. – minmaxavg
I suggest you to edit your question and put results there instead (along with everything else you elaborated on in your comments). Thanks in advance. – minmaxavg

2 Answers

0
votes

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;
0
votes

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.