SexyMF SexyMF - 3 months ago 18
MySQL Question

mysql joining the same table twice running very slow with wrong result

SELECT `apps`.*,
COUNT(all_users.id) AS total_users,
COUNT(active_users.id) AS active_users
FROM `apps`
INNER JOIN `social_users` AS `all_users` ON `all_users`.`app_id` = `apps`.`id`
INNER JOIN `social_users` AS `active_users` ON `active_users`.`app_id` = `apps`.`id`
WHERE `active_users`.`is_active` = 'true'
GROUP BY `id`
ORDER BY `total_users` ASC LIMIT 30
OFFSET 0


I have 2 tables.

apps -> id, name


social_user -> id, app_id, is_active


I want to run a query on apps table and show all the total users and all the active users.

I join social_users twice.

The problem is that if I join it once (no matter which) it runs very fast. the second join kill the performance and the numbers are not accurate at all, I get 180000 users instead of 750.

how can I do it better and faster?

Thanks

Answer
SELECT `apps`.*,
       COUNT(all_users.id) AS total_users,
       SUM(IF(all_users.is_active='true',1,0)) AS active_users
  FROM `apps`
 INNER JOIN `social_users` AS `all_users` ON `all_users`.`app_id` = `apps`.`id`
 GROUP BY `id`
 ORDER BY `total_users` ASC LIMIT 30
OFFSET 0
Comments