We usually try to avoid subselects because sometimes they force the use of a temporary table and limits the use of indexes. But, when is good to use a subselect?
This example was tested over table a (1310723 rows), b, c and d ( 5 rows each) and with MySQL version 5.5 and 5.6.
Let’s suppose we have a query like this:
|
1 |
select a.name,sum(a.count) aSum,avg(a.position) aAVG,b.col1,c.col2,d.col3<br>from <br>a join <br>b on (a.bid = b.id) join<br>c on (a.cid = c.id) join<br>d on (a.did = d.id)<br>group by a.name,b.id,c.id,d.id |
What will MySQL do? First, it will take the entire data set – this means that will go through each row scanning the value of “bid,” “cid” and “did” and then apply the join to each table. At this point it has the complete data set and then it will start to cluster it, executing the sum and the average functions.
Let’s analyze it step by step:
What can we do to optimize this query? We can’t avoid the group by over the 1.3M rows, but we are able to avoid the join over 1.3M of rows. How? We need all of the information from table a for the “group by” but we don’t need to execute all the joins before clustering them. Let’s rewrite the query:
|
1 |
select a.name,aSum,aAVG,b.col1,c.col2,d.col3<br>from <br>( select name,sum(count) aSum ,avg(position) aAVG,bid,cid,did<br> from a <br> group by name,bid,cid,did) a join <br>b on (a.bid = b.id) join<br>c on (a.cid = c.id) join<br>d on (a.did = d.id) |
We see from the above query that we are doing the “group by” only over table a, the result data set of that subquery is just 20 rows. But what about the query response time? The first query took 2.3 sec avg and the optimized query took 1.8 sec average, half a second faster.
What about adding a covering index? The index that we can add will be:
|
1 |
alter table a add index (name,bid,cid,did,count,position); |
The explain plan of both queries shows that it is using just the index to resolve the query.
Now, the response time of the original query is 1.9 sec which is near the time of the optimized query. However, the response time of the optimized query now is 0.7 sec, nearly 3x faster. The cons of adding this index is that we are indexing the whole table and it shows that the index length is near 80% of the data length.
|
1 |
select a.name,sum(a.count) aSum,avg(a.position) aAVG,b.col1,c.col2,d.col3<br>from <br>a join <br>b on (a.bid = b.id) join<br>c on (a.cid = c.id) join<br>d on (a.did = d.id)<br>where c.col2=3<br>group by a.name,b.id,c.id,d.id |
|
1 |
select a.name,aSum,aAVG,b.col1,a.col2,d.col3<br>from <br>( select a.name,sum(count) aSum ,avg(position) aAVG,bid,cid,did,c.col2<br> from a join<br> c on (a.cid = c.id)<br> where c.col2=3<br> group by name,bid,cid,did) a join <br>b on (a.bid = b.id) join<br>d on (a.did = d.id) |
But the differences in times are not as big (original query 1.1 sec and new query 0.9). Why? because the original query will have less data to group by. Adding c.col2=3 to the original query, the amount of data to group by is reduced from 1.3M to 262k. Indeed, if you add more “where” conditions on different tables, the dataset to sort will be smaller and the speed-up will decrease.
Conclusion: We usually add the GROUP BY at the end of queries, and that is ok because the syntax forces us to do it. However, we can use a subquery to group only the data that we need and then perform the joins over other tables. This could speed up some of our GROUP BY queries.
Resources
RELATED POSTS
