Thank you very much I tried that and it worked -----Original Message----- From: cake-php@googlegroups.com [mailto:cake-...@googlegroups.com] On Behalf Of Andras Kende Sent: Wednesday, December 29, 2010 2:58 PM To: cake-php@googlegroups.com Subject: Re: pagination SELECT COUNT(*) query slow
Hello, Indexing the foreign keys would be ideal too.. Also you could override the core paginateCount, so its will not join 6 tables at the count(*) query http://book.cakephp.org/view/1237/Custom-Query-Pagination I did it this way, its handled by the 'extra': controller: $this->paginate = array( 'extra' => array('recursive' => -1), 'limit' => 50, 'contain' => array( 'ForumCategory', 'UserProfile', 'LastUserProfile', ), 'order' => array('ForumTopic.modified' => 'DESC'), 'conditions' => array('ForumTopic.is_active' => 1), 'fields' => array( 'ForumCategory.name', 'ForumTopic.id', 'ForumTopic.name', 'ForumTopic.forum_post_count', 'ForumTopic.modified', 'UserProfile.first_name', 'UserProfile.last_name', 'LastUserProfile.first_name', 'LastUserProfile.last_name', ) ); $this->set('topics', $this->paginate('ForumTopic')); model: public function PaginateCount($conditions = null, $recursive = 0, $extra = array()) { $rec = empty($extra['extra']['recursive']) ? $recursive : $extra['extra']['recursive']; return $this->find('count', array( 'conditions' => $conditions, 'recursive' => $rec, )); } Andras Kende http://www.kende.comn On Dec 29, 2010, at 2:00 PM, arron wrote: > So i have a relationship table that has 6 tables the primary id is > indexed as well as the models that paginate is using . > > > The problem i am having with pagination is when i edit a row or add a > row the SELECT COUNT(*) query takes forever around 11956MS it also > maxes out my CPU for about 8 seconds. I only have 92K records, if i > edit a row or add a row via phpmyadmin it only takes about 7ms. any > suggestions ? Can i turn off the or get rid of the SELECT COUNT(*) > query ? > > Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. > > You received this message because you are subscribed to the Google Groups "CakePHP" group. > To post to this group, send email to cake-php@googlegroups.com > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en