code-dot-org/dashboard — pull request #214 by cpirich (2013-12-09 10:00:55+00:00)
Title: optimization - fixes too many queries

DO NOT MERGE UNTIL WE ARE READY TO TAKE NON-CRITICAL CHANGES

Since this partial is rendered on the signup page and the prizes page, it seemed worthwhile to go back and fix this code.

new code generates this:
(39.3ms) SELECT COUNT(*) AS count_all, prize_provider_id AS prize_provider_id FROM `prizes` WHERE `prizes`.`user_id` IS NULL GROUP BY prize_provider_id
PrizeProvider Load (0.4ms) SELECT `prize_providers`.* FROM `prize_providers` WHERE `prize_providers`.`id` IN (1, 2, 3, 4, 5, 6, 7, 10)

old code generated this:
PrizeProvider Load (0.4ms) SELECT `prize_providers`.* FROM `prize_providers`
(11.0ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 1 AND `prizes`.`user_id` IS NULL
provider
(0.5ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 2 AND `prizes`.`user_id` IS NULL
provider
(8.7ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 3 AND `prizes`.`user_id` IS NULL
provider
(7.5ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 4 AND `prizes`.`user_id` IS NULL
provider
(9.2ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 5 AND `prizes`.`user_id` IS NULL
provider
(12.7ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 6 AND `prizes`.`user_id` IS NULL
provider
(11.4ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 7 AND `prizes`.`user_id` IS NULL
provider
(0.4ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 8 AND `prizes`.`user_id` IS NULL
(0.3ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 9 AND `prizes`.`user_id` IS NULL
(7.0ms) SELECT COUNT(*) FROM `prizes` WHERE `prizes`.`prize_provider_id` = 10 AND `prizes`.`user_id` IS NULL
provider