2

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities.

All tables have a id column as the primary key. student.id is varchar(64) (similar to a UUID in principle), the other 3 id columns are auto-increment integers. No index has been created yet. There are no foreign keys.

Now I want to query the list of students, along with their total activity number.

Here's what I tried, it works, but extremely slow.

SELECT
    `t1`.`id`,
    `t1`.`name`,
    COUNT(`t2`.`id`) AS `totalX`,
    COUNT(`t3`.`id`) AS `totalY`,
    COUNT(`t4`.`id`) AS `totalZ`,
FROM
    `student` AS `t1`
LEFT JOIN `student_x_record` AS `t2` ON (
    `t2`.`student_id` = `t1`.`id`
)
LEFT JOIN `student_y_record` AS `t3` ON (
    `t3`.`student_id` = `t1`.`id`
)
LEFT JOIN `student_z_record` AS `t4` ON (
    `t4`.`student_id` = `t1`.`id`
)
GROUP BY
    `t1`.`id`
ORDER BY
    `t1`.`id`

Currently, there are less than 100 students in the first table, and about 1K entries in the 3 records table. The query runs in about 20 secs on a vps with 100% CPU consumption.

Is there any advice I can try to improve the performance?


Here's the execution plan:

+----+-------------+-------+------------+------+----------------------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys     | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+----------------------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY           | NULL | NULL    | NULL |   45 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL              | NULL | NULL    | NULL | 1166 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL              | NULL | NULL    | NULL |   32 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t4    | NULL       | ALL  | NULL              | NULL | NULL    | NULL |   41 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+----------------------------+------+---------+------+------+----------+----------------------------------------------------+

Seems horrible that all 4 queries were using ALL type. As a newbie, I think I am doing it the wrong way?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.