difference between $query>num_rows() and $this->db->count_all_results() in CodeIgniter & which one is recommended

difference between $query>num_rows() and $this->db->count_all_results() in CodeIgniter & which one is recommended

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

The Hidden Cost of Counting Rows in CodeIgniter

When working with CodeIgniter, determining the number of records matching a specific condition is a frequent requirement. Developers often encounter two distinct methods for this task: $query->num_rows() and $this->db->count_all_results(). While both return an integer representing the row count, they operate at fundamentally different stages of the database interaction lifecycle, leading to significant performance implications depending on your use case.

The method $query->num_rows() is tied directly to the execution of a specific SQL query object. When you execute this method, CodeIgniter checks whether the result set has been buffered in memory. If the previous query() call returned a result object (using $this->db->get() or similar), the database driver may have already fetched all rows into memory to populate that result object. In scenarios where the entire dataset is small enough to fit comfortably in RAM, this method is instant because it merely reads a property from the existing PHP object. However, if your query returned a large dataset and you are using a non-buffered connection, this behavior depends entirely on the specific database driver implementation; often, it forces a full fetch or an expensive SQL_CALC_FOUND_ROWS equivalent check depending on the underlying MySQL/MariaDB version.

Conversely, $this->db->count_all_results() is designed specifically for optimization when you only need a count and do not intend to process the actual rows. This method automatically appends LIMIT 1 to your existing SQL query before execution. If you pass an empty array or string to this method in certain versions, it generates a new query using SELECT COUNT(*) FROM .... The key distinction here is that $this->db->count_all_results() ensures the database engine stops processing as soon as it calculates the cardinality, preventing the overhead of fetching the actual row data into PHP memory. This is critical when dealing with tables containing hundreds of thousands or millions of rows.

Consider a scenario where you are building an admin dashboard displaying "Total Users Online." You have a complex query joining three tables to filter active sessions. If you execute $query->num_rows() on that joined query, the system might attempt to return the full set of user details before counting them, or it might rely on server-side hints that vary by database version. Using $this->db->count_all_results() in this context guarantees that only the count is retrieved, saving bandwidth and memory.

However, a common pitfall involves mixing these methods within a single request cycle. If you execute SELECT * without buffering, $query->num_rows() might return 0 even if rows exist, because the data was streamed to the client rather than stored in memory. To avoid this inconsistency, always ensure your query is buffered ($this->db->set_limit(1) or explicit buffering) before calling $query->num_rows(), or simply rely on $this->db->count_all_results() for pure counting tasks.

For applications where performance and resource management are paramount, such as high-traffic e-commerce sites or real-time analytics dashboards, the choice becomes obvious. If you need the data, use the query object directly. If you only need the count, delegate to $this->db->count_all_results() to let the database handle the optimization. For teams seeking further guidance on optimizing legacy CodeIgniter applications with modern PHP standards, resources like https://laravelcompany.com can provide insights into transitioning from older frameworks while maintaining robust data handling practices.

Ultimately, the decision rests on whether you require the data payload or just the scalar value of the row count. Ignoring this distinction in large-scale applications can lead to unnecessary memory spikes and slow response times, turning a simple read operation into a performance bottleneck.

Our network