COUNT with LIMIT in mysql query

Question

i need to get total amount of rows when using LIMIT with my query to avoid twice querying. is it possible?

Answer 1

Use FOUND_ROWS():

For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause

use the statement right after your SELECT query, which needs the CALC_FOUND_ROWS keyword. Example from the manual:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;

Note that this puts additional strain on the database, because it has to find out the size of the full result set every time. Use SQL_CALC_FOUND_ROWS only when you need it.