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.