⚠ In case you've missed it we are migrating 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 forum is read-only and soon will be archived. ⚠

Improve get_total_results query for big tables with a lot of rows

query optimization model

  • Please log in to reply
1 reply to this topic

#1 genya



  • Members
  • Pip
  • 5 posts

Posted 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

#2 itrainng



  • Members
  • Pip
  • 2 posts

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

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