MySQL count total number of rows

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 to开发者_Go百科tal 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)

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.

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).