Q:

How to count all the rows of a table from a query with some filters who has a limit?

I will try to be more specific, let's suppose that i have this query:
SELECT * FROM users WHERE status = 1 LIMIT 5;
In my table users i have 40 users who has the status = 5, but in my query i'm limiting to 5, and that's fine, that's what i want to do.
But now, i need to count all users (without limit) who has the status = 1, in this case, 40 users has that status.
How can i do that without make another query?
I don't want to make other query 'cause in the query that i'm really working i have more filters, and i think that make double query with the same filters (except for the limit) is spent innecesary resources from my server.
Also i don't want to remove the LIMIT 5 from my query and take 5 in the code 'cause i have too many users with that status, and get all those users just for count it is innecesary,
I think there must be a better way to do it, can you help me?

A:

If you are using a recent version of MariaDB or MySQL 8+, then you may use COUNT as a window function here:
WITH cte AS (
    SELECT *, COUNT(*) OVER () AS cnt
    FROM users
    WHERE status = 1
)

SELECT *
FROM cte
ORDER BY some_column
LIMIT 5;

Note that using LIMIT without ORDER BY is fairly meaningless, because in that case you are not telling the database which five records you want.  Add an ORDER BY clause for best results.

