0

Possible Duplicate:
MySQL pagination without double-querying?

There is a search box in my website, which includes pagination.

To set the pagination I have to know the number of the matches, so I have two options I thought about:

  1. Using 2 queries: a query for the X matches in the page (using limit), and a query to know how matches there are in total.
  2. Using 1 query: get all the matches and then I know the number of rows, and then get the needed rows by using array_slice

I think the solution #2 is better, what do you think?

Community
  • 1
  • 1
Luis
  • 2,949
  • 12
  • 43
  • 58
  • 1
    @Luis: First get the count, then do the query for the data. Is a general principle, you find it in any good database literature. There is nothing wrong in reading a bit before asking around however. – hakre Dec 12 '12 at 16:28

2 Answers2

3

In MySQL you can add SQL_CALC_FOUND_ROWS to your query and then run SELECT FOUND_ROWS();:

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows

Álvaro González
  • 128,942
  • 37
  • 233
  • 325
  • Ok, sounds good, but where do I run `SELECT FOUND_ROWS()` after using `mysql_query()`? – Luis Dec 12 '12 at 16:34
  • You first of all should take care to not use `mysql_query` any longer but upgrade to PDO or mysqli. Technically it's just running a second query. It might work with `mysql_query`, too, but probably it's not supported by the old driver. – hakre Dec 12 '12 at 16:37
  • I'm using CodeIgniter, so I don't use `mysql_query`.. told it just to ask. So, you said what I described at my #1 solution, why don't using my #2 solution? – Luis Dec 12 '12 at 16:42
  • @Luis - It's a regular query like any other of the `SELECT` kind. – Álvaro González Dec 12 '12 at 16:42
  • @Luis - Doesn't CodeIgniter offer a built-in cross-DBMS paginator? – Álvaro González Dec 12 '12 at 16:43
  • @ÁlvaroG.Vicario To using the built-in pagination I should insert the total rows. – Luis Dec 12 '12 at 16:44
1

The answer will really depend on the number of rows you anticipate matching and the size of data stored in the rows.

The traditional approach to pagination would be to use SQL LIMIT clause to return a subset of the overall result set. If you actually needed to know the full number of records (in order to show full number of "pages") then you would need to also make a SELECT COUNT(primary_key_field) FROM table WHERE [search condition] in order to get that information.

This is generally going to gives best results as the COUNT query should be able to execute very quickly and the LIMIT query will keep query performance responsive so long as you have an index on the field you are using for the sort (as you always want an ORDER BY along with LIMIT to guarantee order). This also mean less records you need to keep in memory in your application.

I do suppose there could be some use cases where the single query approach might be better (i.e. not going to have a large number of potential rows, row data size is small), such that you don't have a huge data transfer time of memory usage of retrieving a full result set.

Really, you would have to just test some scenarios in your app and see what works best for you.

Mike Brant
  • 66,858
  • 9
  • 86
  • 97
  • I have something like 50,000 rows at the table. Your solution is to running a query for the results in the actual page and a query to know the total rows, right (it's my #1 solution)? why don't use my #2 solution? I only run a on query. – Luis Dec 12 '12 at 16:48
  • 1
    @Luis Yes I would think for most cases your solution #1 would be most appropriate. With your #2 solution, say you did a query which returned 1000 rows in the result and each row had 1000 bytes of data and you wanted to show 50 results per page. That means such a query would need to transfer 1MB of data and store that data in memory in order to then filter out the 50 or whatever results that you want to show on a particular page. In the #1 solution you would have a couple bytes of data on the first query and then would transfer and keep in memory 50kB to render the same result. – Mike Brant Dec 12 '12 at 18:57