> Lastly, and I'm not sure if this makes a difference, but it feels wrong to specify the activities.user_id = #{id} in the ON clause. It really should be in WHERE... That may not change anything, or it could cause some really bad cross products currently being produced as intermediate results.
Haven't really used mysql in recent years (6+), but I remember once optimizing a query with about 8 inner joins, grouping and a very long where statement. After setting up all the indexes it still didn't perform as we hoped. Moving some of the conditions up into the ON parts just like in that blog post drastically improved performance. It seemed like in the naive approach, mysql really did all the joins first and only in the end started filtering. I was quite surprised that wasn't handled by the query optimizer, but maybe that has improved since then.
I've been consistently surprised by how little optimization MySQL does on joins. As of 5.6, it didn't even eliminate join clauses that weren't referenced in either the select or where clause which should be a pretty basic optimization and one whose absence hurts a lot given MySQL's nested loops join algorithm.
Then, I'd try an INDEX(user_id, activity_type_id) on activities. InnoDB only uses a single index per table IIRC, and that would mean reading the entire table every time.
Lastly, and I'm not sure if this makes a difference, but it feels wrong to specify the activities.user_id = #{id} in the ON clause. It really should be in WHERE... That may not change anything, or it could cause some really bad cross products currently being produced as intermediate results.