CodeIgniter Forums
Pagination and Best way for Optimization of data load - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Pagination and Best way for Optimization of data load (/showthread.php?tid=58436)



Pagination and Best way for Optimization of data load - El Forum - 06-12-2013

[eluser][email protected][/eluser]
Hello, CI Pros:

I wonder what is the best way to paginate large tables? Will the pagination class in CI enough for large data load from mySQL by multiple users at the same time?

For example, 100 users load a table with 100000 rows at the same time.

how can I load only, for example, 100 pages at a time? Or is the jQuery's loading everything then hide and make data "appears like" in pages is a better solution?

Thank you!


Pagination and Best way for Optimization of data load - El Forum - 06-12-2013

[eluser]boltsabre[/eluser]
It really depends on what you're doing with your table. If there are "WHERE" clauses this will slow it down.
You can add indexes on appropriate columns that are involved with joins and where statements, this will help.
You can also cache results, this can also help (but you should do some testing). If/when you get a new entry into your table you just delete your cache/s and rebuild them. If you have new entries really often then you can sometimes tell the user that new entries take 5 minutes to appear and make your caches only valid for 5 minutes.

Can you be a bit more specific on what you're doing?




Pagination and Best way for Optimization of data load - El Forum - 06-12-2013

[eluser][email protected][/eluser]
[quote author="boltsabre" date="1371044332"]It really depends on what you're doing with your table. If there are "WHERE" clauses this will slow it down.
You can add indexes on appropriate columns that are involved with joins and where statements, this will help.
You can also cache results, this can also help (but you should do some testing). If/when you get a new entry into your table you just delete your cache/s and rebuild them. If you have new entries really often then you can sometimes tell the user that new entries take 5 minutes to appear and make your caches only valid for 5 minutes.

Can you be a bit more specific on what you're doing?

[/quote]

Sure, thanks for the quick respond.
For example, I will list up information that involve 100,000 records that have at least 3 or more AND conditions within a WHERE statement plus there is sorting and such functions for each columns. However, since 100,000 is a huge database records load, so we try to load them in pages of, lets say, 100 each, to reduce database connection usage. change of amount of records per page will also be provided.

By the way, I just did some research and seem Ajax JQuery PHP is the only viable solution for this huge amount of data, or the best one for any situation and optimized.

Do you think so?

Thanks!


Pagination and Best way for Optimization of data load - El Forum - 06-12-2013

[eluser]boltsabre[/eluser]
Quote:seem Ajax JQuery PHP is the only viable solution
I don't really know what you mean there, you're talking about a server side language (php), a client side javascript library (jQuery) and a Ajax... do you mean to get the entire DB table and let jQuery do all the sorting on the users machine? If so be VERY careful with this, you'll crash their computer if you try to load to much data into browser memory!!!


Pagination and Best way for Optimization of data load - El Forum - 06-12-2013

[eluser]Unknown[/eluser]
[quote author="[email protected]" date="1371041415"]Hello, CI Pros:

[...]

how can I load only, for example, 100 pages at a time?

[...]

[/quote]

An example would be :

Code:
// get the number of the actual page, if segment 4 isn't set, set to 1
$page = ($this->uri->segment(4)) ? $this->uri->segment(4) : 1;

// get total amount of rows in your table
$total_rows = $this->db->get('YOUR_TABLE')->num_rows();

// items per page
$per_page = 100;

// calculate the starting row
$start_row = $page * $per_page - $per_page;

$query = $this->db->select('WHAT_YOU_WANT')
                  ->from('YOUR_TABLE')
                  ->limit($per_page, $start_row)
                  ->get();

// handle results



Pagination and Best way for Optimization of data load - El Forum - 06-12-2013

[eluser][email protected][/eluser]
[quote author="boltsabre" date="1371049886"]
Quote:seem Ajax JQuery PHP is the only viable solution
I don't really know what you mean there, you're talking about a server side language (php), a client side javascript library (jQuery) and a Ajax... do you mean to get the entire DB table and let jQuery do all the sorting on the users machine? If so be VERY careful with this, you'll crash their computer if you try to load to much data into browser memory!!![/quote]
[removed]promptTag("email");
Hum, I was not so clear about Ajax which is actually everything that I am familiar with lol . So I guess Ajax will not be an issue, its just I never did it before, was afraid of the learning curves~

Btw, when you say sorting will crash clients computer, I was not really meant to sort on clients computer but what I try to do is:

1. load data requested partially from a table with php and generate a queried table like data with PHP and MYSQL on server side
2. somehow, JQuery retrieve that data and display with its own method of pagination without refresh the page
3. process repeat...

Yes, that about it.... Smile


Pagination and Best way for Optimization of data load - El Forum - 06-12-2013

[eluser][email protected][/eluser]
[quote author="Amok93" date="1371050837"][quote author="[email protected]" date="1371041415"]Hello, CI Pros:

[...]

how can I load only, for example, 100 pages at a time?

[...]

[/quote]

An example would be :

Code:
// get the number of the actual page, if segment 4 isn't set, set to 1
$page = ($this->uri->segment(4)) ? $this->uri->segment(4) : 1;

// get total amount of rows in your table
$total_rows = $this->db->get('YOUR_TABLE')->num_rows();

// items per page
$per_page = 100;

// calculate the starting row
$start_row = $page * $per_page - $per_page;

$query = $this->db->select('WHAT_YOU_WANT')
                  ->from('YOUR_TABLE')
                  ->limit($per_page, $start_row)
                  ->get();

// handle results
[/quote]

Thanks! Seems a very useful example!
Do you think I can combine the usage of pagination class for the CI with JQuery and ajax to achieve the no-refresh needed pagination? Would you plz show me some hint and, if possible and not bother so much, some code will be wonderful!

Thank you