It should be count(*), not count (activity_type_id), because count(expressions) evaluates expression for every row returned, whereas what you actually want is the total number of rows (which also happens to be what you get because activity_type_jd always evaluates to true.

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.