spliting search results 10 by 10

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

spliting search results 10 by 10

Post by pedrotuga »

This might been asked like dozens of times, though i performed a searched and didnt find so much.

i want to perform a search in a database and then show the results 10 each time, like google and friends do.

Thats ok, i limit the result, make pagination etc etc...

now my biggest issue is performance.

like... if perform a search that returns 10000 results, 10000 row will be selected.
Is there any way i can get the number of matches without slow down the whole thing?

thanks in advance.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

Gets the first 10 entries:

Code: Select all

SELECT * FROM BLAH LIMIT 0,10
Gets the Next 10 entries:

Code: Select all

SELECT * FROM BLAH LIMIT 10,10
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

The trickiest thing is know how many pages you have in total so that you know the upper boundary for your LIMIT.

I tend to run a very bare bones COUNT(*) query which I test to return the same number of records as the real query before-hand. I then can use this value to generate the page list. I'm sure there are better ways but you need to know how many records you have before you can make one of those lists.
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

Post by pedrotuga »

a count... didnt thought about that... sounds quicker than a select.

i think it should do it...

let me perform some tests.
Post Reply