[SOLVED] the most efficient way to display paged search results using MySQL/PHP

[joey123]

I have a search that looks at many fields and tables. it could potentially return many records. I want to have it paged which requires returning only a few of the records at a time, but it also needs to know the total number of records to determine the number of pages. I have it working now, but was wondering about the most efficient way to do it.

 

below is an example of something similar. it is a database of people, their hobbies, and skills. users can search on all or none of the fields. there is a form where users can enter search information. the next page tests each field to make sure the input is valid and put together a where clause for my query ($sqlwhere) and which tables are needed ($sqltables) based on which fields are being searched on. my actual database could be searching on many more joined tables (10 or so), the one below is simplified.

 

I want to display the results paged with 10 per page. my query returns a record set of the ID's of all records that match the search. then i use the logic below to determine which ones to display and the way to make links to the other pages.

 

 

$sql="select people.ID from $sqltables where $sqlwhere order by people.name";

// run query

// $tot = number of records in the record set

 

$perpage=10;

$totpages=ceil($tot/$perpage);

if ($curpage<1) $curpage=1;

if ($curpage>$totpages) $curpage=$totpages;

$startnum=($curpage-1)*$perpage;

$endnum=$startnum+$perpage;

if ($endnum>$tot) $endnum=$tot;

 

 

then use a for loop to go through the records. create a record set of the needed fields every time through the loop

 

for ($i=$startnum; $i<$endnum; $i++)

{

  // $ID = "ID" for the current record ($i)

 

  $subquerysql="select name, comments from person where ID=$ID";

  // run query

  // display info

 

  $subquerysql="select hobbies.hobby from hobbies, hobby_person where hobby_person.person_ID=$ID";

  // run query

  // loop through query displaying

 

  $subquerysql="select skills.skill from skills, skill_person where skill_person.person_ID=$ID";

  // run query

  // loop through query displaying

}

 

 

i know this is inefficient since i have to run 31 queries when 10 records are returned, one of which will be a complicated one run on a lot of tables. but if i have all of the keys and foreign keys indexed, the rest of the queries should be fast.

 

here are my options, I'm wondering if one is better or if there is one that i am missing.

 

option 1) (what i am currently doing)

- get a record set of just the ID field for every matching record

- run a query at each loop iteration for the other information

- run queries at each loop iteration to get the related information from the foreign tables

- ADVANTAGES – other than the first query, all of the queries should be quick and will return only the needed information

- DISADVANTAGES – A lot of queries

 

option 2)

- run a query to get the number of matching records

- run another query to get all of the information for the needed records only using "limit $perpage, $firstrecord"

- run queries at each loop iteration to get the related information from the foreign tables

- ADVANTAGES – other than the first 2 queries, all of the queries should be quick and will return only the needed information. Less records returned from the main query.

- DISADVANTAGES – A lot of queries, 1 more than the first option. to use "limit", it does't return as many records, but it still has to go through all of the records to order them.

 

option 3)

- run a query that returns all information on every matching record

- run queries at each loop iteration to get the related information from the foreign tables

- ADVANTAGES – fewer queries

- DISADVANTAGES – a potentially very large record set could be returned with only a small amount of information being needed.

 

 

Also, is there some way to get around the sub queries to find the hobbies and skills associated with each person at every loop iteration. it seems that if i tried to put it all into 1 query, the result would be wasteful and confusing (someone with 10 hobbies and skills would return 100 records, and if joe had a lot of text in the comments field, it would be returned for each record). something like this:

---------------

person | comments | skill | hobby

---------------

joe | his is nice | juggling | stamp collecting

joe | his is nice | juggling | video games

joe | his is nice | gymnastics | stamp collecting

joe | his is nice | gymnastics | video games

joe | his is nice | archery | stamp collecting

joe | his is nice | archery | video games

 

 

 

The Database

 

--------

people

--------

ID

name (varchar)

comments (long text)

 

 

--------

hobbies 

--------

ID

hobby (varchar)

 

 

--------

hobby_person 

--------

ID

hobbies_ID

person_ID

 

 

--------

skills 

--------

ID

skillname (varchar)

 

 

--------

skill_person 

--------

ID

skill_ID

person_ID

[fenway]

Well, you can use FOUND_ROWS() and sql_calc_found_rows, but with very large datasets, using a large offset in a limit clause isn't pretty.  Alternatively, if you can "keep track" of the ordering somehow, you can use it in your where clause.

[joey123]

i've never used FOUND_ROWS or sql_calc_found_rows

 

they sound similar to mysql_num_rows and "select count(*) as totalrecords from..."

 

are they the same?

 

 

i don't like keeping track of the ordering. if records are added or deleted as someone is looking through the pages, the pages won't necessarily be right.

for example:

- a user makes a search that returns 120 results (12 pages, 10 per page)

- rather than searching for total number of results on each page, the program searches once and then passes the number of results from page to page

- as the user goes through the pages, a record is added that fits the search criteria. now there are actually 121.

- the program is still passing 120 as the number of results. the 121st record would be on the 13th page. there will never be a link to the 13th page, therefore the user will never be able to see the last record

- then 11 more records are modifed so that they no longer fit the search criteria. there are now 110.

- the program is still passing the number of records as 120. when the user clicks on page 12, there will be no results.

 

 

if the database gets large, it may be worth the trade off. then again, the larger the database, the greater the potential difference between the actual number of records and number that gets passed from page to page.

 

the problem is not with having data that isn't quite up to date. the problem that the number of records is only calculated once and is never updated, while the records that display based on that number are real time.

 

thoughts?

[fenway]

i've never used FOUND_ROWS or sql_calc_found_rows

 

they sound similar to mysql_num_rows and "select count(*) as totalrecords from..."

 

are they the same?

 

No, they're not the same at all... check the refman, one is the server and one in the client.

 

By keeping track, I mean in a given search, not forever.

[joey123]

that is exactly what i was looking for. i didn't know that mysql had a built in way to do that. very cool.

 

 

for everyone looking at this page as a reference it works like this.

 

i am trying to get paged results. so i need the number of results so that i know how many pages to there are to and i need the information for the records on the current page.

 

before i was doing 2 queries, something like this:

"select count(*) as totalrecords from person"

"select * from person limit 50,10"

 

but you can find all of this information in 1 query, then use another query to get this info:

"select SQL_CALC_FOUND_ROWS * FROM person limit 50, 10"

"SELECT FOUND_ROWS() as totalrecords "

 

 

i see what you are saying about making a record set only once per search. i like the idea of it being completely realtime. if i run into issues, i'll switch to doing it that way.

 

thanks a lot.