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?

| improve this question | | | | |

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.