EllisLab text mark
Advanced Search
     
db->count_all_results() VS db->query(“SELECT COUNT…”)
Posted: 05 September 2010 04:34 PM
Avatar
Joined: 2009-12-29
46 posts

Hi,

I’m wondering what way is the most “performance friendly” to count rows matching a where clause.
I just read the user guide and found this:

$this->db->like(‘title’, ‘match’);
$this->db->from(‘my_table’);
echo $this->db->count_all_results();

I’m wondering if the query is fully executed behind, and counted after, or if a “SELECT COUNT” is executed by codeigniter.

Thank you by advance.

sikko

 
Posted: 05 September 2010 07:15 PM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2009-03-21
680 posts

Good question.  I’m not sure of the answer yet, but you can find out easily like this:

$this->db
     
->like('title''match')
     ->
count_all_results('my_table');

echo 
$this->db->last_query(); 

The last_query() method will show you the query generated by the active record class.  Let us know the results, I’m curious.

Thanks.


EDIT: count_all_results() will create a SELECT COUNT(*) query.

 
Posted: 06 September 2010 09:21 PM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2009-12-29
46 posts

cool smile thank you for your help !

Edit: this thread completes the response:http://ellislab.com/forums/viewthread/166535/
Instead of runing “last query”, you can run this: $this->output->enable_profiler(TRUE);

See ya