13

So I have a table that has a little over 5 million rows. When I use SQL_CALC_FOUND_ROWS the query just hangs forever. When I take it out the query executes within a second withe LIMIT ,25. My question is for pagination reasons is there an alternative to getting the number of total rows?

John
  • 9,840
  • 26
  • 91
  • 137

3 Answers3

8

SQL_CALC_FOUND_ROWS forces MySQL to scan for ALL matching rows, even if they'd never get fetched. Internally it amounts to the same query being executed without the LIMIT clause.

If the filtering you're doing via WHERE isn't too crazy, you could calculate and cache various types of filters to save the full-scan load imposed by calc_found_rows. Basically run a "select count(*) from ... where ...." for most possible where clauses.

Otherwise, you could go Google-style and just spit out some page numbers that occasionally have no relation whatsoever with reality (You know, you see "Goooooooooooogle", get to page 3, and suddenly run out of results).

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Yeah some results would return over a million records so that would make sense why the query hangs. Well I guess it should just be either a link to next page or previous page, but dont do the pagination where it allows you to skip to different pages. Which if there are over a million returned I dont think they care to go to page 10k. – John Jan 22 '11 at 03:21
  • @Marc, I know this is an old question, but I find myself facing the question of whether to use `SQL_CALC_FOUND_ROWS` or just remove the `LIMIT` clause from my query. From your answer, it seems like you're saying these should perform the same. Is that a safe assumption to make, or is it more complicate than that? Thanks. – Dominic P Aug 24 '12 at 19:45
  • bit more complicated. while calc_rows does force mysql to do a 'where' match on all the rows, it doesn't actually have to retrieve the entire row, so there's some savings by not having to prep/fetch data that'll just get tossed. makes sense for very large records (many columns), and/or many rows. on smaller sets, the savings won't be as noticeable, but I have no idea where the cutover point is. – Marc B Aug 24 '12 at 19:52
5

Detailed talk about implementing Google-style pagination using MySQL

Surat
  • 51
  • 1
-1

You should choose between COUNT(*) AND SQL_CALC_FOUND_ROWS depending on situation. If your query search criteria uses rows that are in index - use COUNT(*). In this case Mysql will "read" from indexes only without touching actual data in the table while SQL_CALC_FOUND_ROWS method will load rows from disk what can be expensive and time consuming on massive tables.

More information on this topic in this article @mysqlperformanceblog.

Tadas Sasnauskas
  • 2,183
  • 1
  • 22
  • 24
  • 1
    "If your query search criteria uses rows that are NOT in index – Add indexes" – pronebird Aug 15 '15 at 09:06
  • The article you mentioned is from 2007. I mean, seriously, 5 years later you still reference it? So many things have changed in MySQL. – pronebird Aug 15 '15 at 09:10