If you’ve come across this blog post, you’ve likely experienced the requirement that often crops up when paginating results while querying a MySQL database via Java. As a user, the user generally wants to see the first 10 results on one query, i.e. page 1, then the second 10 results on the next query, i.e. page 2, and so on. But what happens in the situation when the part of the application that displays information to the user wants to know the total number of results from the entire results set that without pagination? For example, to create a visual indication of some sort for the total number of results that are available to look through which could be used to display the total number of pages.
Traditionally you would create two completely separate queries against the database, one which includes the LIMIT and OFFSET parameters in the query and another that does not include these parameters. That’s two database connections, running two independent queries such as;
-- Get paginated results for query SELECT * FROM table_name LIMIT 10 OFFSET 30;
Then running;
-- Get total number of results for query, excluding pagination SELECT COUNT(*) AS NumberOfResultsFound FROM table_name;
And you know what, this is a perfectly good approach to take in a lot of scenarios and especially for simple – medium complexity level MySQL queries. Although where this approach soon falls down is when the SQL queries grow and grow, either due to database structure complexities through many table joins or over time as requirements expand. What you’ll soon find yourself doing is replicating the same complex (and time consuming to create/manage at the coding level…) SQL queries that need updating in two places and keeping in sync with zero discrepancies. And this isn’t ideal, lots of duplicated effort and open to errors with the two queries becoming out of sync.
Thankfully, there is a solution to this within MySQL itself, yet if you’ve come across this blog post you’ve probably realised after much searching around that this isn’t particularly well documented either at the MySQL level or at the Java and JDBC level. Hence the reason for writing this up, partially for others, but mainly so I also don’t forget how to do this in the future…
This is where SQL_CALC_FOUND_ROWS and FOUND_ROWS() parts to the queries come in handy.
For those of you reading this as a traditional database administration type person, you’ll likely be rather familiar with MySQL Workbench for administrating a MySQL database. So if you want to do this within MySQL Workbench, you can simply run the two commands sequentially;
-- Option 1 SELECT * FROM table_name; SELECT FOUND_ROWS() AS NumberOfRowsFound; -- OR -- Option 2 SELECT SQL_CALC_FOUND_ROWS * FROM table_name; -- Yes, no comma, that’s correct SELECT FOUND_ROWS() AS NumberOfRowsFound;
Then this will produce the results you desire;
— Option 1;
150 Rows
— Option 2;
150 Rows
Yes, that’s the same information. Great. But you’ll notice that we haven’t added in the pagination aspects to the SQL query yet via the LIMIT and OFFSET query parameters. So let’s take a look at what happens when we do that;
-- Option 1 SELECT * FROM table_name LIMIT 10 OFFSET 30; SELECT FOUND_ROWS() AS NumberOfRowsFound; -- OR -- Option 2 SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 10 OFFSET 30; -- Yes, no comma, that’s correct SELECT FOUND_ROWS() AS NumberOfRowsFound;
Then this will produce the results you desire;
— Option 1;
40 Rows
— Option 2;
150 Rows
What you’ll notice here is the clear difference in the way MySQL handles the data when you add in the SQL_CALC_FOUND_ROWS into the second set of queries that is run. On the first query, when the SQL_CALC_FOUND_ROWS part is not present in the query, the NumberOfRowsFound is the total number of results that takes into account the LIMIT and OFFSET parameters, resulting in 40 rows, i.e. 10 + 30 = 40. Whereas the second query which includes the SQL_CALC_FOUND_ROWS as part of the query, then this completely ignores the LIMIT and OFFSET parameters, resulting in the desired behaviour for calculating the total number of rows within a MySQL query while ignoring the LIMIT and OFFSET parameters within the query. This is nice as this avoids having to run two duplicate queries as mentioned earlier.
But we’ve just done all of the above within MySQL Workbench which is designed specifically to manage MySQL sessions as needed with ease. Now try taking the same approach within your preferred Integrated Development Environment (IDE) via the SQL editor that is in there and you’ll soon see that this no longer works. Why? Well, quite simply IDEs aren’t dedicated MySQL environments, so they have likely cut corners when it comes to implementing the entire functionalities for MySQL within your preferred IDE. If yours works, great, leave a comment letting others know what you use, I’m sure others reading this would also be interested to know what you are using.
Then we move onto the Java level. Taking the traditional approach for a database connection which roughly follows the logic;
- Create JDBC Connection using MySQL Driver
- Create SQLQuery
- Create PreparedStatement object based on the SQLQuery
- Add in the relevant information to the PreparedStatment, replacing the ?s with the actual data
- Execute the PreparedStatement
- Read the ResultsSet and do what you need to do
- Close the ResultsSet
- Close the JDBC Connection
So taking the initial logic from earlier. We first need to run one query, then run a second query that returns the total number of results that doesn’t take into account pagination aspects of the query. Yet if we took the simple approach with Java, which is to run steps 1 – 8 above twice, then you’ll soon notice that the second query returns 0 for the NumberOfFoundRows on the second query, which is not the correct behaviour we are looking for. The reason behind this is because you are running the query as two distinct JDBC Connections, hence, the second query that is run is a new connection to the MySQL database and hence has no reference to what was run on the previous query.
Makes sense? No? Don’t worry. To test this yourself, give it a go. Create 2x pieces of code that replicates the pseudo code for steps 1 – 8 above, with the first query being the SELECT * FROM table_name LIMIT 10 OFFSET 30; and the second query being SELECT FOUND_ROWS(); and you’ll see that the second database query returns 0, which is clearly incorrect.
The reason for this is due to how MySQL handles sessions. And this is where this gets a little bit unclear in the official MySQL documentation, so if anyone has any specific details on this, again, please comment. Based on my own testing, it appears that MySQL has some form of session management, whereby a session is managed when a connection happens to the database. Meaning that we can take advantage of that at the Java level to utilise this.
So instead of steps 1 – 8 above, we take a slightly different approach to exploit MySQL and the SQL_CALC_FOUND_ROWS and FOUND_ROWS() functionality. In a nutshell, we do this by opening a connection, running two SELECT queries, then closing the connection. This allows us to achieve the desired result that we need.
- Create JDBC Connection using MySQL Driver (aka. MySQL Session)
- Create SQLQuery1 – SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 10 OFFSET 30;
- Create PreparedStatement1 object based on the SQLQuery1
- Add in the relevant information to the PreparedStatment1, replacing the ?s with the actual data
- Execute the PreparedStatement1
- Read the ResultsSet1 and do what you need to do
- Close the ResultsSet1
- //then do the same again
- Create SQLQuery2 – SELECT FOUND_ROWS() AS NumberOfRowsFound;
- Create PreparedStatement2 object based on the SQLQuery2
- Add in the relevant information to the PreparedStatment2, replacing the ?s with the actual data
- Execute the PreparedStatement2
- Read the ResultsSet2 and do what you need to do
- Close the ResultsSet2
- Close the JDBC Connection (aka. MySQL Session)
What you’ll notice when you take this approach in your Java code is that your database queries to achieve this will return exactly what you are looking for. i.e.;
- Rows X – Y (based on pagination controlled by LIMIT and OFFSET MySQL parameters)
- NumberOfRowsFound (Total number of rows, ignoring the LIMIT and OFFSET MySQL parameters)
Pretty neat really and this can save a hell of a lot of time when managing SQL queries at the Java and JDBC level when dealing with paginated data.
Still confused? I’m not surprised. Re-read again about 5x times and do some testing at the MySQL (via MySQL Workbench and via your preferred Java IDE) and Java levels. Still confused? Leave a comment J This is so poorly documented on the web, the above is simply from what I have found through extensive testing based on extremely minimal information. Hope this helps J
For completeness, here’s the not so useful official MySQL information on the FOUND_ROWS() option, https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows.
And finally. It is not 100% clear how MySQL manages sessions at the moment looking at the official documentation. I’ll update this blog post as I find more information on the topic. i.e. What happens when multiple users do the same thing that is overlapping;
- User 1 – SELECT * FROM table_name LIMIT 10 OFFSET 20;
- User 2 – SELECT * FROM table_name LIMIT 2 OFFSET 30;
- User 1 – SELECT FOUND_ROWS(); — Does this bring back #1 or #2?
- User 2 – SELECT FOUND_ROWS();– Does this bring back #1 or #2?
For my tests, I have replicated the above scenario by adding in an artificial delay in between the two queries run by User 1, so I could then run the first query against a different table to produce a different number of results. What I found when running this test, is that MySQL is indeed rather smart in this area and manages the data correctly through some form of session management. This means that even when 1 – 4 above are run in this order, the second query for User 1, returns the number of FOUND_ROWS() from the first query for User 1, not the first query for User 2, which is the correct behaviour.
Hope this is of use to others who also come across this challenge.
Michael Cropper