There are lots of articles on the Web about how to optimize paginated displays. The basic problem is: in order to paginate well, you must display the total number of hits to the user:
1...30 (of 54,342)
But finding that total number of hits can be slow. In particular doing
select count(*) in MySQL is
notoriously slow. There are
lots of possible solutions. A promising, MySQL-specific one is
select FOUND_ROWS(). Using this via native SQL is easy. But exposing it inside JPA-QL (which generates its SQL automatically) is tricky. The rules for using it are:
- Clients must run a normal query, using LIMIT for pagination, and prefix it as: select SQL_CALC_FOUND_ROWS * from myTable
- Immediately afterwards, before any other SQL statements are run, clients call select FOUND_ROWS() to obtain the number of rows there would have been if no LIMIT had been set
A further advantage of
SQL_CALC_FOUND_ROWS is that it can be used with
having queries, whereas
count() cannot.
Here's one way to leverage this inside Hibernate-based JPA. We'll be using
Interceptors. Like all the solutions to this problem, this one isn't perfect, but it may suit your purposes:
package com.kennardconsulting.core.hibernate.interceptor;
import org.hibernate.EmptyInterceptor;
import org.hibernate.HibernateException;
import org.hibernate.SessionFactory;
/**
* Hibernate interceptor to make use of MySQL's proprietary <code>SQL_CALC_FOUND_ROWS</code> hint.
* The <code>count()</code> method in MySQL is notoriously slow
* (http://mysqlha.blogspot.com/2009/08/fast-count-for-innodb.html) as it scans the entire index.
* <code>SQL_CALC_FOUND_ROWS</code> was introduced to allow clients to obtain a <code>count()</code>
* in a more performant way.
* <p>
* The rules are:
* <p>
* <ol>
* <li>Clients must run a normal query, using <code>LIMIT</code> for pagination, and prefix it with
* <code>SQL_CALC_FOUND_ROWS</code></li>
* <li>Immediately afterwards, <em>before any other SQL statements are run</em>, clients call
* <code>FOUND_ROWS()</code> to obtain the number of rows there <em>would</em> have been, if no
* limit had been set</li>
* </ol>
* <p>
* A further advantage of <code>SQL_CALC_FOUND_ROWS</code> is that it can be used with
* <code>having</code>, where <code>count()</code> cannot.
* <p>
* Other approaches we tried:
* <p>
* <ul>
* <li>Hibernate supports ScrollableResults, but on MySQL that seems to just bring back the entire
* results and is very slow</li>
* <li>You can try embedding count(*) as an extra column in the query, but that negates the indexes</li>
* <li>You can try 'show table status where name = Member' but that varies wildly and is
* disconcerting for the user (for 60,000 rows, +/- 3,000 rows)</li>
* <li>You can make 'count()' setCacheable( true )</li>
* </ul>
* <strong>Note: this is not a general purpose Interceptor. It assumes it will be used to execute a
* single select (possibly with secondary selects) and then thrown away. It cannot be
* re-used.</strong>
*/
public class MySQLCalcFoundRowsInterceptor
extends EmptyInterceptor {
//
// Private statics
//
private final static String SELECT_PREFIX = "select ";
private final static String CALC_FOUND_ROWS_HINT = "SQL_CALC_FOUND_ROWS ";
private final static String SELECT_FOUND_ROWS = "select FOUND_ROWS()";
//
// Private members
//
private SessionFactory mSessionFactory;
private byte mSQLStatementsPrepared;
private Long mFoundRows;
//
// Constructor
//
public MySQLCalcFoundRowsInterceptor( SessionFactory sessionFactory ) {
mSessionFactory = sessionFactory;
}
//
// Public methods
//
@Override
public String onPrepareStatement( String sql ) {
switch ( mSQLStatementsPrepared ) {
case 0: {
mSQLStatementsPrepared++;
// First time, prefix CALC_FOUND_ROWS_HINT
StringBuilder builder = new StringBuilder( sql );
int indexOf = builder.indexOf( SELECT_PREFIX );
if ( indexOf == -1 ) {
throw new HibernateException( "First SQL statement did not contain '" + SELECT_PREFIX + "'" );
}
builder.insert( indexOf + SELECT_PREFIX.length(), CALC_FOUND_ROWS_HINT );
return builder.toString();
}
case 1: {
mSQLStatementsPrepared++;
// Before any secondary selects, capture FOUND_ROWS. If no secondary selects are
// ever executed, getFoundRows() will capture FOUND_ROWS just-in-time when called
// directly
captureFoundRows();
return sql;
}
default:
// Pass-through untouched
return sql;
}
}
public long getFoundRows() {
if ( mFoundRows == null ) {
captureFoundRows();
}
return mFoundRows;
}
//
// Private methods
//
private void captureFoundRows() {
// Sanity checks
if ( mFoundRows != null ) {
throw new HibernateException( "'" + SELECT_FOUND_ROWS + "' called more than once" );
}
if ( mSQLStatementsPrepared < 1 ) {
throw new HibernateException( "'" + SELECT_FOUND_ROWS + "' called before '" + SELECT_PREFIX + CALC_FOUND_ROWS_HINT +"'" );
}
// Fetch the total number of rows
mFoundRows = ( (Number) mSessionFactory.getCurrentSession().createSQLQuery( SELECT_FOUND_ROWS ).uniqueResult() ).longValue();
}
}
To use this Interceptor inside JPA-QL:
SessionFactory sessionFactory = ((org.hibernate.Session) mEntityManager.getDelegate()).getSessionFactory();
MySQLCalcFoundRowsInterceptor foundRowsInterceptor = new MySQLCalcFoundRowsInterceptor( sessionFactory );
Session session = sessionFactory.openSession( foundRowsInterceptor );
try {
org.hibernate.Query query = session.createQuery( ... ) // Note: JPA-QL, not createNativeQuery!
query.setFirstResult( ... );
query.setMaxResults( ... );
List entities = query.list();
long foundRows = foundRowsInterceptor.getFoundRows();
...
} finally {
// Disconnect() is good practice, but close() causes problems. Note, however, that
// disconnect could lead to lazy-loading problems if the returned list of entities has
// lazy relations
session.disconnect();
}
Hope that helps!
The next release of Metawidget (v1.15) represents a significant rearrangement of our source tree, as we migrate the codebase from Ant to Maven.
Don't Panic!
JBoss have just released 
Blogger 