• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
large query number of rows issue solution patch

#1
[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;
    }

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


Messages In This Thread
large query number of rows issue solution patch - by El Forum - 09-24-2007, 10:39 AM
large query number of rows issue solution patch - by El Forum - 09-24-2007, 11:54 PM
large query number of rows issue solution patch - by El Forum - 09-25-2007, 02:58 AM
large query number of rows issue solution patch - by El Forum - 09-25-2007, 05:25 AM
large query number of rows issue solution patch - by El Forum - 09-25-2007, 09:19 AM
large query number of rows issue solution patch - by El Forum - 09-25-2007, 04:58 PM
large query number of rows issue solution patch - by El Forum - 09-25-2007, 05:33 PM
large query number of rows issue solution patch - by El Forum - 12-30-2009, 08:39 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.