optimizing an sql query using inner join and order by
- by Sergio B
I'm trying to optimize the following query without success. Any idea where it could be indexed to prevent the temporary table and the filesort?
EXPLAIN SELECT SQL_NO_CACHE `groups`.*
FROM `groups`
INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id
WHERE ((`memberships`.user_id = 1)
AND (`memberships`.`status_code` = 1 AND `memberships`.`manager` = 0))
ORDER BY groups.created_at DESC LIMIT 5;`
+----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | memberships | ref | grp_usr,grp,usr,grp_mngr | usr | 5 | const | 5 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | groups | eq_ref | PRIMARY | PRIMARY | 4 | sportspool_development.memberships.group_id | 1 | |
+----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
+--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| groups | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | |
| groups | 1 | index_groups_on_name | 1 | name | A | 6 | NULL | NULL | YES | BTREE | |
| groups | 1 | index_groups_on_privacy_setting | 1 | privacy_setting | A | 6 | NULL | NULL | YES | BTREE | |
| groups | 1 | index_groups_on_created_at | 1 | created_at | A | 6 | NULL | NULL | YES | BTREE | |
| groups | 1 | index_groups_on_id_and_created_at | 1 | id | A | 6 | NULL | NULL | | BTREE | |
| groups | 1 | index_groups_on_id_and_created_at | 2 | created_at | A | 6 | NULL | NULL | YES | BTREE | |
+--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
+-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| memberships | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| memberships | 0 | grp_usr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 0 | grp_usr | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | grp | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | usr | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | grp_mngr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | grp_mngr | 2 | manager | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | complex_index | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | complex_index | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | complex_index | 3 | status_code | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | complex_index | 4 | manager | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 2 | status_code | A | 2 | NULL | NULL | YES | BTREE | |
| memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 3 | manager | A | 2 | NULL | NULL | YES | BTREE | |
+-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
