Mysql – Is SQL_CALC_FOUND_ROWS Safe and Accurate for a Single DB User Environment

MySQL

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?

Best Answer

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

Related Solutions

MySQL – Using Autoincrement ID for Username

With MyISAM, there's a neat trick you can do:

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  PRIMARY KEY (name, id)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 
  AUTO_INCREMENT=1 ;

With the above PRIMARY KEY, if all users have different name, they will be given id = 1. If another user comes and chooses name that already exists, he will get id=2, etc.

UPDATE

It seems I misread your question and you only need to have name = 'U243' for user with id=243. If that's the case, you don't have to store the name at all. Use this (myISAM or InnoDB, it will work for both):

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 
  AUTO_INCREMENT=1 ;

and then, to retrieve the id and name:

SELECT 
    id
  , 'U' || id  AS name
FROM
    test ;

Mysql – How to COUNT number of rows with LIMIT

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. Check out FOUND ROWS

Best Answer

Related Solutions

MySQL – Using Autoincrement ID for Username

Mysql – How to COUNT number of rows with LIMIT

Related Question