I have product records above 60,000 ,
When I fire a query with PHPMyAdmin -> SQL it gets result instantly but when I use same with codeigniter pagination it will lag,
It solves with when counting the records
num_rows() takes 20 seconds
where as
$this->db->select('COUNT(*) as count') with get()->row()
it will take just 0.63 seconds
I would also highly recommend turning on ‘profiler’ for CI to determine where the bottle neck actually is.
$this->output->enable_profiler(TRUE);
Thirdly if I am not able to get any additional information back based on your profiler I would alternately recommend you using jQuery datatables in conjunction with CI, its server side processing can handle millions of rows within a short time. It will only load the page that is currently active to make things a lot more simpler and easier on the users end.
2
Answers
It solves with when counting the records
num_rows()
takes 20 seconds where as$this->db->select('COUNT(*) as count')
with get()->row() it will take just 0.63 secondsGenerally the speed of your queries are based on indexes and references. I would suggest that you check that first.
You can use the ‘Explain’ statement to get a better idea as to how to optimize your query. Here is more information directly from the MySQL site.
Using Explain Statement
I would also highly recommend turning on ‘profiler’ for CI to determine where the bottle neck actually is.
Thirdly if I am not able to get any additional information back based on your profiler I would alternately recommend you using jQuery datatables in conjunction with CI, its server side processing can handle millions of rows within a short time. It will only load the page that is currently active to make things a lot more simpler and easier on the users end.
https://datatables.net/examples/data_sources/server_side