Paginating technique? which is better

Tea_J

Ok, say we have 50 records. we want to show 10 at a time.. what would be the best way to process this..

1) Using the LIMIT function?

$query = "SELECT * FROM customers LIMIT $start,$end";

SELECT * FROM customers LIMIT 0,10

2) Using the mysql_data_seek() function?
eg
$query = "SELECT * FROM customers";

$results = mysql_query($query);

mysql_data_seek($results,$startRow-1);

I ask because, mysql_data_seek seems to be more convinient specially in complex filtering situations.. however, thinking about selecting ALL records first whithout limits then parsing the 10 current rows seems to be overkill or something.. Say i had 10,000 records.. and i need to show 10 each time.. the 2) method seems to be slower or inefficient because it retrieves ALL the records regardless of the display requirements..or doest it not work this way?

Would luv to hear your thoughts.

tea

TheDefender

I can't speak for everyone, but I have a site with a directory containing 7500+ names, and I use the LIMIT method (#1), and it runs very smooth and fast... Of course, I have other filters in place so as to not have to deal with all 7500+ records, and never really get more than 100 on any given return, but during the testing, I had it wide open on the entire recordset, and it worked great.

Tea_J

I see. I've always used the LIMIT method.. but since i found out about mysql_data_seek i started using that since it seems to be easier... for one, i dont have to count all the records in the table first before equating the startRow and endRow values.. it seems to be working smoothly like LIMIT method.. but then again , i only have 500 records to play with...

would like to hear other EXPERIENCES and thoughts.

tea

drawmack

Well if you're using mysql then doing a count query is pretty quick (especially without any filters since it's just a look up at that point). Also you only have to count the records once and you can store it in a session variable since you have to check for out of bounds errors anyway. Also you can't use mysql_data_seek with mysql_unbuffered_query, which means you've got to retrieve the entire dataset. This will effect scalability in two ways. First if your site gets a lot of traffic it will slow down as your buffers fill with query data. Second as your data set grows your buffers will fill faster. All in all I think that limit is the way to go.

Tea_J

great comment here.. any more ideas?

How about the mysql_data_seek people? No one gona defend this?

I insist because I see a lot of commercial PHP applications that uses the 2nd method... I think the famous forum software InvisionBoard as well as vBulletin itself (the one here) are using the 2nd option.... (not sure but i think i looked into this already)...

sooo...

tea

drawmack

Originally posted by Tea_J
I insist because I see a lot of commercial PHP applications that uses the 2nd method... I think the famous forum software InvisionBoard as well as vBulletin itself (the one here) are using the 2nd option.... (not sure but i think i looked into this already)...

Well count queries are uberfast on mysql but uberslow on most other databses. So if you're designing a system that can work off of multiple databases just by loading different class files (i.e. InvisionBoard) then the benefits of cross db compatibility would probably out weigh the performance drawbacks and make mysql_data_seek the way to go.

Tea_J

ahh.. now a something to dispute about..

ok, so about mysql_data_Seek, is it really that of an overkill to select all rows first? I mean, is it really that a big a difference with LIMIT which only calls the rows to be displayed.. say in a situation with 10,000 records..

Well if you're using mysql then doing a count query is pretty quick (especially without any filters since it's just a look up at that point). Also you only have to count the records once and you can store it in a session variable since you have to check for out of bounds errors anyway.

Well, i will have filters in place. I have sorting also.. im just using the simple select statement for this purpose

Also you can't use mysql_data_seek with mysql_unbuffered_query, which means you've got to retrieve the entire dataset. This will effect scalability in two ways. First if your site gets a lot of traffic it will slow down as your buffers fill with query data. Second as your data set grows your buffers will fill faster. All in all I think that limit is the way to go.

Unbuffered? does this mean it doesnt pull out all the records like a normal query?

tea

drawmack

Originally posted by Tea_J
ok, so about mysql_data_Seek, is it really that of an overkill to select all rows first? I mean, is it really that a big a difference with LIMIT which only calls the rows to be displayed.. say in a situation with 10,000 records..

Let's take a look at a little math to answer this one.

Situation:
You have 10,000 records that you're displaying 10 at a time and there are 100 concurent users on your system.

Buffer Space:
If you select all 10,000 rows every time then you have 10,000 * 100 = 1,000,000 rows of data in your buffers to handle this.

If you select only the needed 10 rows every time then you have 10 * 100 = 1,000 rows of data in your buffers to handle this.

If you use mysql_unbuffered_query then you only have 100 rows in your buffers to handle this.

Well, i will have filters in place. I have sorting also.. im just using the simple select statement for this purpose

MySQL is optimized for data retrieval, so it's still going to be very fast to do a count query.

Unbuffered? does this mean it doesnt pull out all the records like a normal query?

[man]mysql_unbuffered_query[/man]

Tea_J

Hmmmmm.. great info.. interesting..

Buffer Space:
If you select all 10,000 rows every time then you have 10,000 * 100 = 1,000,000 rows of data in your buffers to handle this.

Is this damaging/terrible/bad as it sounds? Or is it ok really?

tea

drawmack

Originally posted by Tea_J
Is this damaging/terrible/bad as it sounds? Or is it ok really?

How much data (bit wise) is in each record that's in the buffer?

How large are your buffers?

For speed and scalability you should ALWAYS minimize buffer usage.

Tea_J

How much data (bit wise) is in each record that's in the buffer?

well, just about 6 varchar columns and a medium text column.

How large are your buffers?

gosh. i dunno.. default mysql installation...

tea

tsinka

Hi,

which MySQL version is installed ? Starting at MySQL 4.0.0 you can get the total number of rows without executing a count(*) query.

Example:

$res = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM atable WHERE awhereclause LIMIT 50,10");
$res2 = mysql_query("SELECT FOUND_ROWS()");
$row = mysql_fetch_row($res2);
$numRows = $row[0];

while ($row = mysql_fetch_array($res)) {
  // do something
}

In most cases that should be faster than executing a COUNT query and the main query.

Executing the count query once and storing the result in the session is faster but might lead to problems if the contents of the table change (DELETE or INSERT) after storing the count in the session. In that case you might have the wrong number of pagination links.

Thomas

drawmack

Originally posted by tsinka
Executing the count query once and storing the result in the session is faster but might lead to problems if the contents of the table change (DELETE or INSERT) after storing the count in the session. In that case you might have the wrong number of pagination links.

Since the page to view comes from the user, you have to validate it for out of bounds anyway. Therefor you would just move the out of bounds validation to the number of rows in the query insead of the actual page as the validation of the page can easily be extrapolated from the validation of the number of rows in the query. It's really not any less work on the database, unless you're very creative.