FOUND_ROWS () of MySQL is a function that runs in relation to the local or global session?

3

I do not understand much of this MySQL session question, however to solve a need I had to use the command FOUND_ROWS() , my query looked 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 lot of competition (multiple concurrent users running their queries, is SELECT FOUND_ROWS(); related to SELECT of each user individually or related to the last SELECT of the server?

I'm afraid to end up having an asynchrony and end up running SELECT FOUND_ROWS(); on SELECT wrong.

    
asked by anonymous 27.03.2018 / 15:42

1 answer

3

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

Functions like FIND_ROWS , last entered ID 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 be a problem if you make more than one parallel query on the same connection. For example, it is common for an application to open a connection only, and you do this type of query on separate threads. In this case, it would be best to examine the returned result, using something from the client language, or doing some sort of command serialization.

Some things should be noted:

 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, FOUND_ROWS will return all found, regardless of the LIMIT clause, when you enable the SQL_CALC_FOUND_ROWS option, and respecting LIMIT in the first case. / p>

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 previous query gave error, its result is undefined.

A situation that would give problem would be replication based on commands, not on rows, then MySQL looks for line (values):

  

FOUND_ROWS () is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication

Manual:

  

link


Note: I even imagine that MySQL query planner is "smart" enough to, in the case of a query in the SELECT format ... ; SELECT FOUND_ROWS .. in a string only never give a problem, but did not want to hit this key very much because the client languages do not encourage multiple queries even for security reasons, and because I do not have found nothing that states this officially. On the other hand, if you are going to use the MySQL command-line client, it is good to note that it will not be a problem either, since each instance is a connection anyway.

    
27.03.2018 / 16:16