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

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

[eluser]imzyos[/eluser]
:-S im not really sure, but if i will count i never use

Code:

select * from table

maybe it returns a lot of rows, and it will save into a variable but calling just:

Code:

$this->db->query->('select count(*) as rows from table')

SQL just return one row, CI is so fucking good, we need learn SQL not improve another functions

[eluser]vijinho[/eluser]
the advantage of this code is that if you dont specify a table (because you set up your db query to query across several) you can still count the rows without pulling all of them back as is presently needed with active record.

e.g.this model class gets a list of fields to order in a certain way and can return the row count only for that query which is executed using active record:

Code:

function getUsersDetails($fields = array(), $orderby = null, $sortdir = null, $rowcountonly = null, $limit
= null, $offset = null)
    {
        $fields = join(',', $fields);
        $this->db->select($fields);
        $this->db->from('users');
        $this->db->join('users_data', 'users.username = users_data.username', 'left');
        if (!empty($orderby)) {
            $sortdir = (empty($sortdir)) ? '' : 'DESC';
            $this->db->orderby($orderby, $sortdir);
        }

        // do we want to just return the row count?
        if (!empty($rowcountonly)) {
            return $this->db->getNumberOfRows();
        }
        
        // select these rows
        if (!empty($limit) && !empty($offset)) {
            $this->db->limit($limit, $offset);
        } else {
            if (empty($limit)) {
                $limit = $this->config->item('limit_users');
            }
            $this->db->limit($limit);
        }
        
        // get them
        $query = $this->db->get();
        if ($query->num_rows() == 0) {
            return false;
        }
        
        // process the rows
        $users = $query->result_array();
        foreach ($users as $k => $user) {
            $users[$user['username']] = $user;
            unset($users[$k]);
        }
        $query->free_result();
        return $users;
    }

vijay

[eluser]xwero[/eluser]
I don't get it either. If like you say you query several tables how will the function know which table you want the count from. The code only grabs the first table result but what if you want the second or the third?

[eluser]imzyos[/eluser]
maybe if you show and example...

[eluser]vijinho[/eluser]
ok i see what you mean.

what if you want to count data from fields selected from only one table? no it doesn't handle that situation.

in that case i'd need to rewrite the

// replace the fields list for something less complicated
$sql = 'SELECT COUNT(1) AS rows ' . strstr($sql, 'FROM');

as another query which executes a row count within the database on the results of what $sql contains

and once that's done, the problem of reading into memory to get the row count is solved, no?

v

[eluser]vijinho[/eluser]
This is the fix i found by replacing the line

$sql = 'SELECT COUNT(1) AS rows ' . strstr($sql, 'FROM');

with

$sql = 'SELECT COUNT(*) AS rows FROM (' . $sql . ') results';

so whatever columns from whatever table are selected for the active record query, we get the number of results for that.

any other issues with this?

v

[eluser]Shiro[/eluser]
for the getNumberOfRows , only work for single table count, if my query join 2 or more table, the strstr function will break my SQL,

but these following code is better than above one +1 to this code.
[quote author="vijinho" date="1190728736"]the advantage of this code is that if you dont specify a table (because you set up your db query to query across several) you can still count the rows without pulling all of them back as is presently needed with active record.

e.g.this model class gets a list of fields to order in a certain way and can return the row count only for that query which is executed using active record:

Code:

function getUsersDetails($fields = array(), $orderby = null, $sortdir = null, $rowcountonly = null, $limit
= null, $offset = null)
    {
        $fields = join(',', $fields);
        $this->db->select($fields);
        .......

vijay[/quote]