SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Pagination: count/LIMIT queries or just one?

    I've got a PHP class for paginating results of a database query, with page numbers and all the usual trimmings, but there's only one query involved - getting the number of pages for the pagination links, and then navigating through the query to get the results in the area I want.
    This involves fetching ALL the records in the table into memory though, and I'm concerned about the overhead this incurs. It's not the scripting, it's more the system architecture I guess .. would having all the records in memory perform better than doing a count(*) query on the data, and then using LIMIT (a second query) to only actually pull the required data into memory.

    I'm sure there's arguments for and against, especially when the database gets big. But is there a winner, between the two? I've always tended towards the one-query method because you need the record count anyway, and even in a LIMIT query, MySQL has to traverse all the rows anyway?

    Can anyone give me some advice? thanks in advance!

    Cheers,
    Alex ...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    with a LIMIT query, mysql does not have to read all the rows if the ORDER BY is on columns that have indexes

    the only time you'd want to return all rows is if you're using query results caching
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Really? Interesting. So there's no need to pull all the rows into memory, then? Sorted.

    Is there some way to get the total number of rows without a count query, too?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about FOUND_ROWS() ?

  6. #6
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    on a related note, is it faster / better to do a COUNT(*) type query or a COUNT(primary_key_field) type query?

    I would have though the latter.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    FOUND_ROWS requires a query which uses LIMIT, and is based on the number of rows that would've been returned without the LIMIT, which is sematically different (and therefore potentially different in execution time) from a COUNT() query

    COUNT(*) and COUNT(column) are different, the former ignores nulls, so the latter is usually slower because it actually has to scan the values in the column

    however, if column is the primary key, it can't be null, so it's possible (but not guaranteed) that the optimizer is smart enough to realize this, and change it to a COUNT(*)

    note that COUNT(*) can use any index on the table, but COUNT(column) can only use an index if there exists an index on that column, and if there does not, it has to scan the table

    so COUNT(*) is usually faster, and never slower
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that r937.

    Seems counter-intuitive to me when the column used is the primary key, but from now on I'll be using COUNT(*).

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, well, except if what you really need is COUNT(column), i.e. excluding nulls

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question FOUND_ROWS used for pagniation?

    FOUND_ROWS requires a query which uses LIMIT, and is based on the number of rows that would've been returned without the LIMIT, which is sematically different (and therefore potentially different in execution time) from a COUNT() query
    r937, according to what you say, then FOUND_ROWS is ideal for pagination, no? Say you're retrieving all the posts for a particular forum from a posts table, there might be 100 records for that particular forum, and you're showing them 20 at a time, you'd still be paginating (not displaying) 100 records, not 20. Did I get that right?

    Alex ...

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •