Speed and pagination

[Nutter]

I've got a site set up where I have data being pulled from a MySQL database. The data is put into tables that are paginated.

My question is this: to handle pagination it appears I need to run two queries. One to run the actual query with ORDER BY and LIMIT. The second to get the COUNT(). Both have the same WHERE clause.

Is there a good way to handle this without running two queries? A few of them have multiple JOINs and that sort of thing. It's working ok now while in development, but ultimately the site should become busier (I hope). It seems like running multiple complex queries is something I want to avoid if possible.

Thanks,
- Ryan

[mincklerstraat]

You'll probably want the two queries, SELECT with the LIMIT, and SELECT COUNT. These are two different types of info, so you need two queries. With the first, mysql sees the LIMIT, and just goes and gets the rows which match that limit, and when it's done, it's done, it doesn't need to do anything else. With the second, it doesn't need to go grab any rows at all; it just needs to know the number of results that match the query, so it's usually fairly lightweight and doesn't hit your db too hard / slow things down too much.

The alternatives would be:

to use one SELECT query without the limit, and use PHP to only display the relevant results - this would probably be a much 'heavier' processing option and would slow things down.

Make your LIMIT +1 the limit that you'd normally use, just don't display the LIMIT +1 entry; check to see if it's present with

mysql_num_rows($handle)

or

count($array)

, and if it is, you know you can display your 'next>>' link, and all the previous page links you want. If it's not there, don't display the 'next>>'. Some sites with paginated articles seem to use this technique - phpbuilder, iirc for example - you can go ahead to the next page, and on subsequent pages, there are links to each previous page; however, on page 1, you can't go directly to page 3 or page 4.

Also see [webmasterworld.com...] .

[Nutter]

Thanks - The select & limit paired with the select count() is what I'm doing now. I prefer to have 'Page x of y' with the previous / next links.

Just wanted to see if there was any faster way.

- Ryan

[coopster]

It sounds like a job for SQL_CALC_FOUND_ROWS and FOUND_ROWS() [dev.mysql.com] but they are only available starting at MySQL 4.0.0.

[mincklerstraat]

Now that's real handy! Three cheers to coopster for keeping up on mysql development. So many php folks are still writing for 3.2x, e.g. queries that can't be cached.

[Nutter]

That's exactly the sort of thing I was looking for. Now, I just have to get it put in :-)

[Nutter]

If anyone is interested, I did a little benchmarking between using the FOUND_ROWS() method and the SELECT COUNT(x) method. The queries are pretty close to what I'm needing, so that's why they are what they are. I know they use SELECT *, but on several of my queries I needed every field, so I wanted the 'worst case' test.

Total records that match the WHERE clause is 232.

I ran each query 100 times, so the actual query time is 1% of what I show.

1 - SELECT * FROM table WHERE field LIKE '%searchstring%'
1.4918 seconds. This was just a baseline. It returned all the records, which I didn't want.

2 - SELECT * FROM table WHERE field LIKE '%searchstring%' LIMIT 0, 10
0.2326 seconds, also a baseline - This time it returned the records I wanted to display.

3 - Same primary query as #2, but added: SELECT COUNT(id) FROM table WHERE field LIKE '%searchstring%'.
1.0427 seconds. The primary query returned the 10 records I wanted and the count query returned 232.

4 - SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE field LIKE '%searchstring%' LIMIT 0, 10 - followed by SELECT FOUND_ROWS().
0.8999 seconds. A little more than a 10% increase in speed over using COUNT(x). Like was said above, this requires MySQL v4.

I don't know how useful (or new) this information is to y'all, but I figured I'd try and save anyone the 30 minutes I spent benchmarking :-)

- Ryan

[Warboss Alex]

Cheers Nutter!

Finally my host's upgraded to MySQL 4. FOUND_ROWS rocks.

[dmmh]

nm...I missed something

[dmmh]

how do I get an actual number from this query SELECT SQL_CALC_FOUND_ROWS?

doesnt quite seem to work for me :(

[Nutter]

The first query returns the records you're after. You just need to add the SQL_CALC_FOUND_ROWS so that it'll keep track.

The second query - SELECT FOUND_ROWS() - will return one record with one field. So:

$row = mysql_fetch_array(mysql_query("SELECT FOUND_ROWS()")); 
echo $row[0];

will show the total number found. Same idea as if you had used COUNT(x), but it appears to be faster this way.

- Ryan

[dmmh]

thats what I did. works one one page, doesnt on another...tad odd

thanks though