FOUND_ROWS() from Mysql is a function that runs relative to local or global session?

Question

I don’t understand much of this Mysql session issue, but to resolve a need I had to use the command FOUND_ROWS(), my query was like this:

SELECT * FROM LICENCAS where FK_PEDIDO = 9479174 AND LICENCA_DATA_EMISSAO BETWEEN '2018-02-26 19:45:05' AND '2018-03-26 19:45:05';SELECT FOUND_ROWS();

The question is: If I put this into production, and have a big competition (multiple simultaneous users running their queries, SELECT FOUND_ROWS(); is related to SELECT of each user individually or related to the last SELECT server?

I’m afraid I’ll end up having an asynchrony and end up executing SELECT FOUND_ROWS(); in the SELECT wrong.

Answer 1

Your doubt is a valid concern, good indicator that you are reasoning to program, anticipating situations.

The functions as FIND_ROWS, last ID inserted and others of the same type are almost all stored by connection. Even if it is in the same application, if you have two connect stored in different pointers, each works independently of the other.

It will only give problem if you in the same connection make more of a parallel query. For example, it is common for an application to open only one connection, and you do this kind of query in threads separate. In this case, it would be better to examine the returned result, using something from the client language, or to do some sort of command serialization.

Some things should be noticed:

 SELECT coluna FROM tabela WHERE id = 100 LIMIT 20,50;

 SELECT SQL_CALC_FOUND_ROWS coluna FROM tabela WHERE id = 100 LIMIT 20,50;

In the above case, the FOUND_ROWS will return all found, regardless of the clause LIMIT, when activating the option SQL_CALC_FOUND_ROWS, and respecting the LIMIT in the first case.

In addition, it should be used immediately following the query from which you want to get the value. Note that if you need the value after that, you need to store it somehow.

Another thing: if the query previous gave error, its result is undefined.

A problem situation would be command-based replication, not line-based replication, so Mysql looks for line-based replication (values):

FOUND_ROWS() is not replicated reliably using statement-based Replication. This Function is Automatically replicated using Row-based Replication

Handbook:

https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-Rows

Note: I even imagine that the query Planner Mysql be "smart" enough to, in the case of a query in the format SELECT ... ; SELECT FOUND_ROWS.. in a string only never give problem, but did not want to hit this key much because the client languages do not encourage multiple darlings even for the sake of safety, and the fact that I haven’t found anything to state that officially. On the other hand, if you are going to use the Mysql command line client, it is good to note that there will also be no problem, because each instance is a connection, anyway.