MySQL count total number of rows

Question

I have a complicated MySQL query which takes a lot of time, selecting from a table with more than 150k rows and several JOINS and subqueries. I am limiting the amount of results, but I need to know the total amount of rows.

Is there a way not to repeat the query with COUNT(*) as the field to get them? (repeating the query almost doubles the amount of time the script takes to complete)

Answer 1

MySQL has a clause called SQL_CALC_FOUND_ROWS which you put after SELECT and before any field names. Then you call a second query which is just SELECT FOUND_ROWS() AS rows and it gives you how many rows the previous query found.

Answer 2

It may actually be faster to make the two queries (so you may just want to do some quick testing):

• http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

In any case, try to make sure that the COUNT(*) can use the index (it will say "using index" in the Extra column if you use EXPLAIN query)

You could also consider caching the COUNT(*) result if it doesn't have to be exact (as you're limiting your result).