A
A
alexfyodrv2019-10-20 20:05:40
MySQL
alexfyodrv, 2019-10-20 20:05:40

Is it possible to improve such a simple query?

At the moment, the query execution time is 0.5 seconds and this is a long time. Can you tell me if this query can be improved to make it faster? Perhaps you have ideas?

SELECT
    `experience`.`role` AS role,
    COUNT(*) AS count_users
FROM
    `users`
LEFT JOIN
    `experience`
ON
    `experience`.`user_id` = `users`.`id`
GROUP BY
    `role`
ORDER BY
    `count_users`
DESC

There is already an index on the user_id field in the experience table. InnoDB tables. This query counts the number of users by role.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-10-20
@alexfyodrv

SELECT `experience`.`role` AS role, COUNT(*) AS count_users
FROM `experience`
GROUP BY `role`
UNION
SELECT 'Not role', 
  ( SELECT COUNT(*) FROM `users` ) - ( SELECT COUNT( DISTINCT( `user_id` )) FROM `experience` )
ORDER BY 2 DESC

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question