Skip to content

Instantly share code, notes, and snippets.

@mikecmpbll

mikecmpbll/slow_count.sql

Last active August 29, 2015 13:56
Show Gist options
  • Save mikecmpbll/8998464 to your computer and use it in GitHub Desktop.
Save mikecmpbll/8998464 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
slow_count.sql
-- SELECT distinct `students`.`id` -- takes ~8ms
SELECT count(distinct `students`.`id`) -- takes 6.4seconds!
FROM `students`
INNER JOIN `categories_students` ON `categories_students`.`student_id` = `students`.`id`
INNER JOIN `categories` ON `categories`.`id` = `categories_students`.`category_id`
LEFT OUTER JOIN `groups_students` ON `groups_students`.`student_id` = `students`.`id`
LEFT OUTER JOIN `groups` ON `groups`.`id` = `groups_students`.`group_id` AND `groups`.`group_type_id` = 1
left outer join (
select student_id
from (
select gs.student_id, g.id as group_id
from groups_students gs
inner join groups g on g.id = gs.group_id
inner join group_types gt on gt.id = g.group_type_id
where gt.permission_group
) s
left outer join (
select g.id as group_id
from groups_users gu
inner join groups g on gu.group_id = g.id
inner join group_types gt
on gt.id = g.group_type_id
where gu.user_id = 137
and gt.permission_group
) u
on s.group_id = u.group_id
group by s.student_id
having count(*) = count(u.group_id)
) as class_permissions on class_permissions.student_id = students.id
WHERE (
students.id = class_permissions.student_id
or not exists (
select * from groups_students
inner join groups on groups.id = groups_students.group_id
inner join group_types on group_types.id = groups.group_type_id
where group_types.permission_group
and groups_students.student_id = students.id
)
) AND `categories`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8)
AND `students`.`archived` = 0
-- EXPLAIN (query with count):
+----+--------------------+---------------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+-----------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+-----------------------------------------------+------+---------------------------------+
| 1 | PRIMARY | categories_students | ALL | index_categories_students_on_student_id | NULL | NULL | NULL | 22 | Using where |
| 1 | PRIMARY | categories | eq_ref | PRIMARY,index_categories_on_id | PRIMARY | 4 | my_db.categories_students.category_id | 1 | Using index |
| 1 | PRIMARY | students | eq_ref | PRIMARY,index_students_on_id | PRIMARY | 4 | my_db.categories_students.student_id | 1 | Using where |
| 1 | PRIMARY | groups_students | ref | index_groups_students_on_student_id_and_group_id | index_groups_students_on_student_id_and_group_id | 4 | my_db.categories_students.student_id | 9 | Using index |
| 1 | PRIMARY | groups | eq_ref | PRIMARY,group_type_id | PRIMARY | 4 | my_db.groups_students.group_id | 1 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1144 | Using where |
| 5 | DEPENDENT SUBQUERY | groups_students | ref | index_groups_students_on_group_id_and_student_id,index_groups_students_on_student_id_and_group_id | index_groups_students_on_student_id_and_group_id | 4 | my_db.students.id | 9 | Using index |
| 5 | DEPENDENT SUBQUERY | groups | eq_ref | PRIMARY,group_type_id | PRIMARY | 4 | my_db.groups_students.group_id | 1 | |
| 5 | DEPENDENT SUBQUERY | group_types | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1145 | Using temporary; Using filesort |
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 46 | |
| 4 | DERIVED | gu | ref | index_groups_users_on_user_id,index_groups_users_on_group_id | index_groups_users_on_user_id | 5 | | 49 | Using where |
| 4 | DERIVED | gt | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer |
| 4 | DERIVED | g | eq_ref | PRIMARY,group_type_id | PRIMARY | 4 | my_db.gu.group_id | 1 | Using where |
| 3 | DERIVED | gt | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where |
| 3 | DERIVED | g | ref | PRIMARY,group_type_id | group_type_id | 5 | my_db.gt.id | 649 | Using where; Using index |
| 3 | DERIVED | gs | ref | index_groups_students_on_group_id_and_student_id | index_groups_students_on_group_id_and_student_id | 4 | my_db.g.id | 10 | Using index |
+----+--------------------+---------------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+-----------------------------------------------+------+---------------------------------+
-- EXPLAIN (without count)
+----+--------------------+---------------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+-----------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+-----------------------------------------------+------+---------------------------------+
| 1 | PRIMARY | categories_students | ALL | index_categories_students_on_student_id | NULL | NULL | NULL | 22 | Using where; Using temporary |
| 1 | PRIMARY | categories | eq_ref | PRIMARY,index_categories_on_id | PRIMARY | 4 | my_db.categories_students.category_id | 1 | Using index |
| 1 | PRIMARY | students | eq_ref | PRIMARY,index_students_on_id | PRIMARY | 4 | my_db.categories_students.student_id | 1 | Using where |
| 1 | PRIMARY | groups_students | ref | index_groups_students_on_student_id_and_group_id | index_groups_students_on_student_id_and_group_id | 4 | my_db.categories_students.student_id | 9 | Using index; Distinct |
| 1 | PRIMARY | groups | eq_ref | PRIMARY,group_type_id | PRIMARY | 4 | my_db.groups_students.group_id | 1 | Distinct |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1144 | Using where; Distinct |
| 5 | DEPENDENT SUBQUERY | groups_students | ref | index_groups_students_on_group_id_and_student_id,index_groups_students_on_student_id_and_group_id | index_groups_students_on_student_id_and_group_id | 4 | my_db.students.id | 9 | Using index |
| 5 | DEPENDENT SUBQUERY | groups | eq_ref | PRIMARY,group_type_id | PRIMARY | 4 | my_db.groups_students.group_id | 1 | |
| 5 | DEPENDENT SUBQUERY | group_types | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1145 | Using temporary; Using filesort |
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 46 | |
| 4 | DERIVED | gu | ref | index_groups_users_on_user_id,index_groups_users_on_group_id | index_groups_users_on_user_id | 5 | | 49 | Using where |
| 4 | DERIVED | gt | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer |
| 4 | DERIVED | g | eq_ref | PRIMARY,group_type_id | PRIMARY | 4 | my_db.gu.group_id | 1 | Using where |
| 3 | DERIVED | gt | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where |
| 3 | DERIVED | g | ref | PRIMARY,group_type_id | group_type_id | 5 | my_db.gt.id | 649 | Using where; Using index |
| 3 | DERIVED | gs | ref | index_groups_students_on_group_id_and_student_id | index_groups_students_on_group_id_and_student_id | 4 | my_db.g.id | 10 | Using index |
+----+--------------------+---------------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+-----------------------------------------------+------+---------------------------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment