large query number of rows issue solution patch

[eluser]vijinho[/eluser]
Calling $query = $this->db->get() and then $query->num_rows() is inefficient.

This is because calling $this->db->get() will take all the query results and put them in memory before counting them. If you just want the count, it's a resource-intensive way of getting the number of rows.

I made a patch where instead of calling $this->db->get() you call $this->db->getNumberOfRows(); and it returns you the number of rows.

Then if it's not too many rows you can call $this->db->get() after to do more processing.

This is a patch to /system/database/DB_active_rec.php

Code:

// --------------------------------------------------------------------

    /**
     * GetNumberOfRows
     *
     * Compiles the select statement based on the other functions called
     * and runs the query, returning ONLY the row count
     *
     * @access    public
     * @param    string    the limit clause
     * @param    string    the offset clause
     * @return    object
     */
    function getNumberOfRows($table = '', $limit = null, $offset = null)
    {
        if ($table != '')
        {
            $this->from($table);
        }
        
        if ( ! is_null($limit))
        {
            $this->limit($limit, $offset);
        }
            
        $sql = $this->_compile_select();
        $this->_reset_select();
        
        // replace the fields list for something less complicated
        $sql = 'SELECT COUNT(1) AS rows ' . strstr($sql, 'FROM');  
        
        // return the count value
        $query = $this->query($sql);
        $result = $query->result_array();
        if (is_array($result)) {
            $result = $result[0]['rows'];        
        }
        $this->_reset_select();
        return $result;
    }

    // --------------------------------------------------------------------