Jump to content


Member Since 21 Jul 2018
Offline Last Active Jul 30 2019 06:53 PM

#149191 Improve get_total_results query for big tables with a lot of rows

Posted genya on 14 June 2019 - 01:55 PM



The original Grocery_crud_model gets the total number of rows by performing the exact same query without the 'LIMIT x' portion

and then counts the number of rows with codeigniter's num_rows() function. For small tables this approach does not

seem noticeable time wise.


I had a table with more than one hundred thousand (100k+) rows and the list would render kind of slow. To improve this,

I made adjustments to Custom_grocery_crud_model from https://www.groceryc...-and-searching/


I added to the model 'SELECT COUNT(primary_key) FROM (' + original query + ')' for the get_total_results function, this increased time of rendering by about 4x.


Example in controller:

	public function my_table()
 		$crud = new grocery_CRUD();
		$crud->columns('col1', 'col2', 'col3');
                        SELECT primary_key, col1, col2, another_table.col3
                        FROM my_table
                        LEFT JOIN another_table ON (another_table.id = my_table.id)
		', 'GROUP BY my_table.col1', $query_binding=array());

                // for searching with WHERE clause on col3
			'col3' => 'another_table',
		// for making the second query of getting the row count faster
                // The alias for COUNT has to be num_rows as that is the name used in the model.
			SELECT COUNT(a.primary_key) as num_rows
		', ') a');
		$output = $crud->render();

		$this->load->view('my_view.php', $output);

I also added the set_column_table() function for searching on those columns from other tables by WHERE, and there is a query binding argument to the custom query if needed.


I attached the model file.


I hope this helps.

Attached Files