Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
2  
And indeed, in some instances, the 2 query route is faster –  Derek Downey 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. –  Levi Hackwith 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. –  marcioAlmada Oct 4 '11 at 17:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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