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:


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?


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

  • 2
    And indeed, in some instances, the 2 query route is faster Oct 4 '11 at 17:29
  • When you say users do (User A and User B) do you mean web app users or DB users? My application has multiple web app users but when they log in they are all querying the database using the same single DB user account. Oct 4 '11 at 17:30
  • @LeviHackwith, I mean the web app final users. Also, don't worry about the DB user, it's quite normal to use the same DB user to execute queries through all the application so it's not a matter in the final result.
    – marcio
    Oct 4 '11 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.