I've been working on trying to optimize my code for a client before we decide to throw more hardware at the problem. One thing that I have noticed is the way that COUNT(*) queries are generated for pagination where the controller FIND method includes a JOIN table. For example here's one of the queries generated via the CakePHP pagination:
SELECT COUNT(*) AS `count` FROM `trades` AS `Trade` left JOIN ratings AS `Rating` ON (`Trade`.`cusip` = `Rating`.`cusip`) WHERE `Trade`.`trade_date` = '2009-09-11' AND `Trade`.`state_id` = 5; In this case, it seems like LEFT joins should not be included in the COUNT query because it has no effect on the records returned. After some testing, there is a performance hit: mysql> SELECT COUNT(*) AS `count` FROM `trades` AS `Trade` left JOIN ratings AS `Rating` ON (`Trade`.`cusip` = `Rating`.`cusip`) WHERE `Trade`.`trade_date` = '2009-09-11' AND `Trade`.`state_id` = 5; +-------+ | count | +-------+ | 4771 | +-------+ 1 row in set (0.10 sec) And now WITHOUT the JOIN: mysql> SELECT COUNT(*) AS `count` FROM `trades` AS `Trade` WHERE `Trade`.`trade_date` = '2009-09-11' AND `Trade`.`state_id` = 5; +-------+ | count | +-------+ | 4771 | +-------+ 1 row in set (0.00 sec) Ideally LEFT joins should not be included in the COUNT(*) query for pagination. I could be wrong here but just looking to get some opinions. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
