+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: CI3 - count_all_results() is slow (/showthread.php?tid=82168)
CI3 - count_all_results() is slow - futurewebs - 06-20-2022
Im using count_all_results() in CI3 to create the max results for pagination.
My main query that has the limits set and returns the full result is very quick and optimised / indexed etc
The issue is that I also run a version of the main query with count_all_results() instead of get() and this is often pretty slow.
Is there a better way to do this or am i just missing a trick ?
All my tables use InnoDB on MariaDB 10.3
Thanks in advance for your help
RE: CI3 - count_all_results() is slow - InsiteFX - 06-20-2022
You could try [b]num_rows() which will return the number of rows in the result set.[/b]
PHP Code:
$cnt = $query->num_rows();
RE: CI3 - count_all_results() is slow - futurewebs - 06-21-2022
Hi, thanks for the reply.
The way i have things set up is that the same function can return either the result array or the count depending on what i ask for. The main result with all the details is mostly being limited to a page of results (say 25) but the complete set might have 100,000 results and so unless i can empty the select part of the query and not include the limit part when doing a count and then just do num_rows it returns a huge amount of data and exceeds memory limit.
for example
PHP Code:
function somequery($rs_type ='std', $limit=0, $offset=0)
{
$this->db->select('stuff, stuff, stuff');
$this->db->where('more stuff');
$this->db->limit($limit, $offset);
if ($rs_type == 'count'){
$result = $this->db->count_all_results('table');
} else {
$query = $this->db->get('table');
$result = $query->result();
}
return $result;
}