We are using Spring (Eclipselink) on MariaDB. Our SQL via ORM result in a long lasting DB query. Therefore I need to refine it into a nativequery - which is no big deal by itself. Nevertheless the Resultset is limited by a LIMIT and I need a total counter for all found records. For querying the total counter I found for MariaSQL the following solution.
My question:
Is it save to query the two SQL command separately or should I send them once with a UNION combined?
The question arises due to the fact that between my query and the SELECT FOUND_ROWS() the another query might interfere (with a request from the same Microservice) and dilute the result.
If both queries are done in the same transaction, the MVCC of INNODB should guarantee, that the results will not be influenced by other transactions.
see: https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html