CodeIgniter count too slow - pagination

Question

I'm trying to make a search using CodeIgniter faster. I use the pagination library and I have to count the records returned from the query on a table which contains more than 1.2 million records. The num_rows() function is very slow ( takes approximately 3 seconds )

public function search()
{
    $this->output->enable_profiler(TRUE);
    
    $data = array();
    
    $query = $this->input->get('query');
    $filter = $this->input->get('f');
    $hd = $this->input->get('hd');

    if($hd == 'true'):
        $this->db->where('hd',1);
    endif;

    $request = urldecode($query);
    
    $where = "MATCH (name,tags) AGAINST ('".$request."' IN BOOLEAN MODE)";
    $this->db->where($where);   

    $get_vars = $this->input->get();

    if(is_array($get_vars) && ($this->input->get('query')) ):
        $config['suffix'] = '?'.http_build_query($get_vars,'', '&');
    endif;

    $config['base_url'] = base_url('search');
    $config['per_page'] =  8;
    $config['num_links'] = 8;
    $config['full_tag_open'] = '<div class="pagination"><ul>';
    $config['full_tag_close'] = '</ul></div><!--pagination-->';
    $config['first_link'] = '&laquo; First';
    $config['first_tag_open'] = '<li class="prev page">';
    $config['first_tag_close'] = '</li>';
    $config['last_link'] = 'Last &raquo;';
    $config['last_tag_open'] = '<li class="next page">';
    $config['last_tag_close'] = '</li>';
    $config['next_link'] = 'Suivant &rarr;';
    $config['next_tag_open'] = '<li class="next page">';
    $config['next_tag_close'] = '</li>';
    $config['prev_link'] = '&larr; Précédent';
    $config['prev_tag_open'] = '<li class="prev page">';
    $config['prev_tag_close'] = '</li>';
    $config['cur_tag_open'] = '<li class="active"><a href="">';
    $config['cur_tag_close'] = '</a></li>';
    $config['num_tag_open'] = '<li class="page">';
    $config['num_tag_close'] = '</li>';
    
    $query = clone $this->db;
    $config['total_rows'] = $query->get('videos')->num_rows();      
    $config['segment'] = $this->uri->segment(2);
    $this->pagination->initialize($config);

    $data['results'] = $this->db->get('videos',$config['per_page'],$this->uri->segment(2))->result();

    $this->load->view('search',$data);
}

Any solutions?

http://stackoverflow.com/questions/12864557/select-count-vs-mysql-num-rows — Atural, Sep 29 '16 at 11:35
You could just get the data, put it into a table, and use js pagination. — James Lalor, Sep 29 '16 at 12:05
`num_rows()` probably pulls every single record from the DB and uses PHP's `count()` function on the result set. The most efficient thing you can do is to run a dedicated `select count(*) as total_videos from videos` query and use that value. — MonkeyZeus, Sep 29 '16 at 12:31
@JamesLalor That sounds like a sure-fire idea to help make things worse... — MonkeyZeus, Sep 29 '16 at 12:32
@MonkeyZeus datatables can utilise json data to do pagination on large data sets, and doesn't get all data at once. — James Lalor, Sep 29 '16 at 15:20
@JamesLalor Fascinating. Please elaborate on how it solves the slowdown when counting 1.2 million records so that DataTables can properly tell you what page your are viewing vs the total pages available. — MonkeyZeus, Sep 29 '16 at 15:30
@MonkeyZeus from what I understood the whole issue of him getting the count was for pagination, regarding the title he put and the fact he mentioned the pagination library, so I offered my solution for paginating his records. Datatables would have it's own json endpoint set up to manage and utilise pagination and searching, seems like a better alternative that to try and count all the millions of records and process html generation server side. — James Lalor, Sep 29 '16 at 15:52
@MonkeyZeus https://datatables.net/extensions/scroller/examples/initialisation/server-side_processing.html - https://datatables.net/examples/data_sources/server_side.html — James Lalor, Sep 29 '16 at 15:52

score 2 · Accepted Answer · answered Sep 29 '16 at 13:34

2

As the comments from @MonkeyZeus suggests, performance will improve by using the SQL count() function. Codeigniter provides this through the Query Builder function count_all_results($table = '', $reset = TRUE).

The function will take into account any restrictors you have put in place e.g. where, or_where, like, etc.

Change this

$query = clone $this->db;
$config['total_rows'] = $query->get('videos')->num_rows();

to this

//next line is not needed because the query will not be reset by the row count
//$query = clone $this->db;
$config['total_rows'] = $this->db->count_all_results('videos', FALSE);

I'd be interested in knowing how execution time changes.

answered Sep 29 '16 at 13:34

DFriend

8,869
1
13
26

@VeenZ Could you benchmark your queries and narrow down which query is slowing things down? If you need help with properly benchmarking then let me know. – MonkeyZeus Sep 29 '16 at 15:27
@VeenZ no thank you, I do not use Skype. You should try posting a new question asking about query optimization because it's possible you need to add or fix up some indexes or re-arrange your query. – MonkeyZeus Sep 29 '16 at 15:54

CodeIgniter count too slow - pagination

1 Answers1