mysql query optimization with multiple groupings or order by

Question

UPDATE: Table and index definition

desc activities;x
+----------------+--------------+------+-----+---------+  
| Field          | Type         | Null | Key | Default |  
+----------------+--------------+------+-----+---------+  
| id             | int(11)      | NO   | PRI | NULL    |  
| trackable_id   | int(11)      | YES  | MUL | NULL    |  
| trackable_type | varchar(255) | YES  |     | NULL    |  
| owner_id       | int(11)      | YES  | MUL | NULL    |  
| owner_type     | varchar(255) | YES  |     | NULL    |  
| key            | varchar(255) | YES  |     | NULL    |  
| parameters     | text         | YES  |     | NULL    |  
| recipient_id   | int(11)      | YES  | MUL | NULL    |  
| recipient_type | varchar(255) | YES  |     | NULL    |  
| created_at     | datetime     | NO   |     | NULL    |  
| updated_at     | datetime     | NO   |     | NULL    |  
+----------------+--------------+------+-----+---------+  

show indexes from activities;

+------------+------------+-----------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+  
| Table      | Non_unique | Key_name                                            | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type |  
+------------+------------+-----------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+  
| activities |          0 | PRIMARY                                             |            1 | id             | A         |        7263 |     NULL | NULL   |      | BTREE      |  
| activities |          1 | index_activities_on_trackable_id_and_trackable_type |            1 | trackable_id   | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_trackable_id_and_trackable_type |            2 | trackable_type | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_owner_id_and_owner_type         |            1 | owner_id       | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_owner_id_and_owner_type         |            2 | owner_type     | A         |        7263 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_recipient_id_and_recipient_type |            1 | recipient_id   | A         |        2421 |     NULL | NULL   | YES  | BTREE      |  
| activities |          1 | index_activities_on_recipient_id_and_recipient_type |            2 | recipient_type | A         |        3631 |     NULL | NULL   | YES  | BTREE      |  
+------------+------------+-----------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+  

select count(id) from activities;  
+-----------+  
| count(id) |  
+-----------+  
|      7117 |  
+-----------+  

This is what my current query looks like:

SELECT act.*, group_concat(act.owner_id order by act.created_at desc) as owner_ids 
FROM (select * from activities order by created_at desc) as act 
INNER JOIN users on users.id = act.owner_id 
WHERE (users.city_id = 1 and act.owner_type = 'User') 
GROUP BY trackable_type, recipient_id, recipient_type 
order by act.created_at desc 
limit 20 offset 0;

Doing an explain

I have played around with this query a lot including indexes etc. Is there any way to optimizes this query?

Answer 1

MySQL sometimes work weirdly so I will give this a shot. I'm assuming ID is Primary Key on users table.

SELECT 
    act.trackable_type, act.recipient_id, act.recipient_type,
max(act.created_at) as max_created_at,
    group_concat(act.owner_id order by act.created_at DESC) as owner_ids 
FROM  activities act 
WHERE act.owner_id in (select id from users where city_id = 1)
AND act.owner_Type = 'User'
GROUP BY trackable_type, recipient_id, recipient_type 
ORDER BY max_created_at
LIMIT 20

Answer 2

I think you don't need offset 0 at all, looks like you can live without subquery too. If you don't use fields from users table, you can use in (or exists) to make it clear:

select
    a.trackable_type, a.recipient_id, a.recipient_type,
    max(a.created_at) as max_created_at,
    group_concat(a.owner_id order by a.created_at desc) as owner_ids 
from activities as a
where
    a.owner_type = 'User' and
    a.owner_id in (select u.id from users as u where u.city_id = 1)
group by a.trackable_type, a.recipient_id, a.recipient_type
order by max_created_at desc
limit 20;

Also for me looks like your query could definitely get performance boost if you create index on owner_type, owner_id on activities (your index owner_id, owner_type will not work well for your query) and index on city_id on users.