MySQL efficient query left join and group by (plus index)

mysql efficient query

MySQL sacrifices group by to increase the speed of left join (assuming an index is added).

User table: 100,000 data

Example 1: About 200 seconds

SELECT U.id, A.favorite_count FROM (SELECT id from user) U
LEFT JOIN (
  -- Number of likes SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count
  FROM favorite
  GROUP BY favorite_by
) A ON U.id=A.user_id
LEFT JOIN (
  -- Number of comments SELECT user_id, COUNT(*) AS comment_count
  FROM photo_comment
  GROUP BY user_id
) B ON U.id=B.user_id

Example 2: More than 1 second

select uf.user_id , uf.favorite_count, COUNT(pc.id) as comment_count from (
select u.id as user_id , SUM(f.favorite_count) as favorite_count from (SELECT id from user) u 
LEFT JOIN favorite f on f.favorite_by = u.id  
GROUP BY u.id
) 
LEFT JOIN photo_comment pc on pc.user_id = uf.user_id
GROUP BY uf.user_id

Appendix: How to efficiently join 3 tables in MySQL

For the following three tables join statement

select * 
from t1 
join t2 on(t1.a=t2.a) 
join t3 on (t2.b=t3.b) 
where t1.c>=X and t2.c>=Y and t3.c>=Z;

If rewritten as straight_join, how to specify the connection order and how to create indexes for the three tables?

Try to use the BKA algorithm

When using BKA, instead of "calculating the result of joining two tables first and then joining with the third table", the query is directly nested. Specific implementation: Among the three conditions of t1.c>=X, t2.c>=Y, and t3.c>=Z, select the table with the least data after filtering as the first driving table. At this point, the following two situations may occur.

If table t1 or t3 is selected, the rest is fixed:

• If the driving table is t1, the connection order is t1->t2->t3, and an index must be created on the driven table field, that is, create an index on t2.a and t3.b
• If the driving table is t3, the join order is t3->t2->t1, and indexes need to be created on t2.b and t1.a.
  

At the same time, we also need to create an index on field c of the first driving table.

In the second case, if the first driving table selected is table t2, the filtering effects of the other two conditions need to be evaluated.

The idea is to try to make the data set of the driving table participating in each join as small as possible, because this will make our driving table smaller.

Summarize

This is the end of this article about MySQL efficient query left join and group by. For more relevant MySQL efficient query content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!