Persistent connection: MySQL results FOUND_ROWS ()

Question

As a background background in MySQL with the SQL_CALC_FOUND_ROWS flag and the FOUND_ROWS () function, MySQL allows you to get the total number of rows that will be returned if SELECT did not use LIMIT, without having to issue a second heavy query:

$query = "SELECT SQL_CALC_FOUND_ROWS * from movies WHERE.... LIMIT 20"; $res1 = $db->query($query); $numrows = $db->query('SELECT FOUND_ROWS()')->fetchColumn(); 

This can be useful for pagination. Suppose you use a persistent connection:

 try{ $db = new PDO('mysql:host=localhost;dbname=' . $dbname, $user, $pass, array( PDO::ATTR_PERSISTENT => true ) ); etc. 

If two users click almost simultaneously, is there a way that requests could cross paths and could get the data requested by others?

Answer 1

No, it's not a problem.

As for your PHP page, it has its own unique connection throughout the page. The only difference with a persistent connection from a non-persistent connection is that after the page exits, the connection does not break, but returns to the pool for transfer to another page. In other words, this is not a general connection, it is a reconnection. If there are 35 pages serving the page request at the same time, your database will still have 35 simultaneous connections.

However, you should be careful about propagating the connection state (for example, incomplete transactions), but this is a separate issue.