Best Method for "# to # of #" Procedure

Anon

Hi all,

Sorry for looking like an FNG, but I just need some advice.

What's the best way for an "# to # of #" search results page. I've thought of two methods that would work, and I need to know which one is better performance wise (With PHP and MySQL):

Two queries - a "SELECT COUNT(*) FROM ... WHERE ... " and the regular Query "SELECT ... FROM ... WHERE ... " with a LIMIT.
One query and only display # to # with code.

I'd also be interested to see any other methods.

Many thanks.

Anon

Use the LIMIT clause. Only selecting the data that you need. Selecting anymore data than is necessary is inefficient and pretty much sloppy.

brandonschnell

to get the total, rather than using count(*) use count(id). id being the column unique id.

displaying # to # is better in performance. but annoying for users as it doesn't give them an idea of how much further they have to go. if you know the total and the current range, you can group the results into pages of so many results. ie, click number 1 to see the first 10 results, click number 5 to see results 50 - 60, etc.