I have a MySQL query that goes as follows
SELECT count(`clicks`.`user_id`) as total, `users`.`fullname` FROM `users`, `clicks`, WHERE `users`.`id` = `clicks`.`user_id` GROUP BY `clicks`.`user_id` ORDER BY `total` desc LIMIT 0,20;
I am running statistics on several button pressing type games. It has a user table and a clicks table. It logs clicks from a specific user. The user can click the button whenever they want. Twenty clicks one day, thirty another, etc. They aren t all in a row.
Currently there are about ~180k clicks for ~2k users. This query takes 1.38 seconds to run on average. I would like to speed it up if that s at all possible.