Jump to content


Improve get_total_results query for big tables with a lot of rows

query optimization model

  • Please log in to reply
No replies to this topic

#1 genya

genya

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 14 June 2019 - 01:55 PM

Hello,

 

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->set_model('custom_grocery_crud_model');
		
		$crud->set_table('my_table');
		$crud->columns('col1', 'col2', 'col3');
		
		$crud->basic_model->set_custom_query('
                        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
		$crud->basic_model->set_column_table(array(
			'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.
		$crud->basic_model->set_header_footer_sql('
			SELECT COUNT(a.primary_key) as num_rows
			FROM
			(		
		', ') 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







Also tagged with one or more of these keywords: query, optimization, model

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users