CI3 - count_all_results() is slow

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

You could try [b]num_rows() which will return the number of rows in the result set.[/b]

PHP Code:

$cnt = $query->num_rows(); 


What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

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;
}