Is SQL_CALC_FOUND_ROWS Safe and Accurate for a Single DB User Environment?

Question

I have a large MySQL web application that uses a single user for all of its database queries. We have multiple databases running off of a master-slave replication setup.

My question is this: We use SQL_CALC_FOUND rows to get the number of records returned from a query as if the query had been run without a LIMIT clause; we do this for pagination on our front-end. My concern is that, if two users of the web application login and run two queries simultaneously (remember that every user is using the same DB user account to run queries), when we query for the number of found rows:

SELECT FOUND() ROWS AS total

There may be a non-zero chance that the number being returned is from a different query that was run at the same time as the first query. Is this a possibility? Could the above query return an inaccurate number or is there some sort of fail-safe in place?

Answer 1

Well, it might not be a complete in depth analysis but, from a developer perspective, I can say it depends on how your server handle the requests and execute the application. To be more clear: synchronously or asynchronously.

If your server handle requests synchronously, than there's nothing to worry. In this case, given two hypothetic users (User A and User B) making requests at the same time, your server will attend only one request first, All the processing for the given hypothetic requests are going to happen in a different moment so there will be no information crossover madness.

But if your application is running in many server instances or in a server that handles requests asynchronously, I wouldn't suggest you to rely the presented method to count rows.

Even in cases when my assumption is not 100% accurate, for a sake of sanity, in your application, I would suggest you to use the good old common bulletproof double query solution:

SELECT [fields] FROM [tables] WHERE [filter]

and later our count query...

SELECT count(*) as total FROM [tables] WHERE [filter]

There's no way to go wrong and, as @DTest added, this 2 query approach can be faster in some environments (see the link in his comment).

Oh, and I took a fast read at MySQL documentation related to Information Functions, there it was on a tiny little silly notice:

Important: FOUND_ROWS() is not replicated reliably, and should not be used with databases that are to be replicated.

from: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count