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?