⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Improve get_total_results query for big tables with a lot of rows



genya
  • profile picture
  • Member

Posted 14 June 2019 - 13: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 /topic/3620-custom-queries-with-pagination-sorting-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.


itrainng
  • profile picture
  • Member

Posted 17 January 2020 - 07:12 AM

I love the ease and beauty of Grocery Crud. That said, I have refrained from using it in my business because it cannot handle large amounts of data without slowing to a crawl.

 

Thank you for addressing a major gap in the Grocery Crud Platform. That gap is that Grocery Crud chokes on enterprise level database tables with thousands of records. I plan to adapt your fix for a table I have that has over 100 columns and 100,000 rows. I will appreciate it if you can simplify your controller and produce a copy WITHOUT the MySQL joins from different tables. Thank you