Is there a faster way to count records on a join...

Question

This query gets me the profiles with the most evidence records when the profile is the actor. Is there any way to make it faster in a mysql query?

SELECT profiles.*, count(*) AS counted_profiles 
FROM `profiles` 
INNER JOIN (SELECT ev.actor_id 
            FROM evidences AS ev 
            WHERE ev.actor_type = 'Profile') AS ev2
ON ev2.actor_id = profiles.id 
GROUP BY ev2.actor_id 
ORDER BY counted_profiles DESC LIMIT 10

Answer 1

You could remove profiles.*, remove subselect, and add an index for join fields.

Also, consider this:

SELECT ev.actor_id, count(*) AS counted_profiles 
FROM `profiles` INNER JOIN evidences AS ev
      ON ev.actor_id = profiles.id AND ev.actor_type = 'Profile'
GROUP BY ev.actor_id 
ORDER BY counted_profiles DESC LIMIT 10

Answer 2

You could use the GROUP BY, ORDER BY and LIMIT clauses in the subquery, then INNER JOIN with that. That's just another way to do it, not sure of the performange gain though.