Z|NET Development

MySQL has the notion of specifying a specific index to use when invoking queries in order to override the default provided by MySQL. Typically, MySQL selects the most optimized index based on the query. However, in some cases, it fails to pick properly reducing performance. In those cases, you need to use the keyword “use index” to select the index. For example:

SELECT [FIELDS] FROM my_table TABLE USE INDEX (my_index) WHERE [predicate]

The problem with this is when moving to an ORM-solution such as Hibernate. Being provider-independent, there is no capability natively within Hibernate and HQL to use provider-specific features such as “use index”. However, Hibernate does allow extension points to do everything from adding provider-specific functions to completely rewriting the SQL statements themselves. In this article, I will demonstrate the capability to semi-add “use index” to hibernate.

Before reading take note that this is, IMO, a really ugly hack to make this work. Unfortunately, in my use case, I had the desire to keep my query caches within HQL in line with the timestamps cache created by hibernate for table updates. The easiest way to actually utilize “use index” is to use a native SQL query rather than HQL. Native SQL queries within hibernate can contain any vendor-specific functionality. Further, there may be better ways at doing this, but I had not found any. Thus, this is my hack and so without further ado, let’s get to it.

Hibernate has two extension points that we will use to provide this functionality. The first is a custom dialect used to add specific functions, keywords, etc. The second is an interceptor to intercept various functionality such as preparing SQL statements. When I initially went down this path, I tried to just create and register a custom function in a custom dialect and then invoke it such as:

SELECT [FIELDS] FROM MyTable TABLE useindex(my_index)

However, from what I understand, you may only use custom functions within the where, group, and order clauses, not the from clause. So, I decided to try the interceptor and modify the SQL before getting added to the SQL statement. Unfortuately, Hibernate only passes in the SQL so there is no way to know whether the specified query wanted an index or not. So, I decided to mix the two by using a custom function within a where statement that I could then find in the SQL in order to inject the proper syntax.

The first step here is to create a custom dialect to register our custom function. The easiest way to do this is to just extend the dialect you are already using so that you merely add functionality to your existing dialect. For example, in my persistence.xml file (NOTE: I am using JPA with Hibernate, but this would work with plain Hibernate and session factory configuration as well), I specify my dialect via:

<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />

So, I just need to create a new class and extend that MySQL5InnoDBDialect. Within the constructor, I just need to register the simple useindex marker function. The result is:

package com.znet.hibernate.dialect;
 
import org.hibernate.Hibernate;
import org.hibernate.dialect.function.StandardSQLFunction;
 
public class MySQL5InnoDBDialect extends org.hibernate.dialect.MySQL5InnoDBDialect
{
    public MySQL5InnoDBDialect()
    {
        super();
        registerFunction("useindex", new StandardSQLFunction("useindex", Hibernate.BOOLEAN));
    }
}

We use the org.hibernate.dialect.function.StandardSQLFunction class to create a quick function that expects the specified function name and expects a return type of boolean. To use this dialect, just update the configuration to specify the newly created class.

<property name="hibernate.dialect" value="com.znet.hibernate.dialect.MySQL5InnoDBDialect" />

This function will in the end take two parameters. The first parameter will denote the table to associate the index to. The second parameter will denote the actual index [eg: useindex(table, my_index)]. This will allow us to mark the resultant SQL with both the index and associated table. Thus, we can properly restructure the generated SQL accordingly. We could now use this function within HQL such as:

String hql1 = "select table from MyTable table where useindex(table, my_index) is true and table.feature is not null";
// result: select [omitted] from my_table table where useindex(table.id, my_index)=1 and table.feature is not null";
// note that hibernate automatically resolves table into table.id within the function

Now that we know what the SQL gets generated as, let’s build an interceptor to transform the SQL. org.hibernate.Interceptors can perform all types of functionality within Hibernate. For our case, we just care about transforming the SQL via the onPrepareStatement(String sql) method. The first step is to create our interceptor. The easiest way is to just extend EmptyInterceptor and merely override the methods you care about. EmptyInterceptor provides empty stubs for all implemented methods.

package com.znet.hibernate.interceptor;
 
import org.hibernate.EmptyInterceptor;
 
public class IndexInterceptor extends EmptyInterceptor
{
    private static final long serialVersionUID = 1L;
 
    @Override
    public String onPrepareStatement(String sql)
    {
        while (true)
        {
            // check if function specified
            int idx = sql.indexOf("useindex(");
            if (idx < 0) { break; }
 
            // find end of function
            int endidx = sql.indexOf(")=1", idx);
            if (endidx < idx) 
            { 
                throwError("expected useindex(table, index) is true"); 
            }
 
            // get both parameters
            String[] params = sql.substring(idx + 9, endidx).split(",");
            if (params.length != 2) 
            {
                throwError("expected 2 parameters to useindex(table, index)");
            }
 
            // trim parameters and verify
            String tableId = params[0].trim(); 
            String indexHint = params[1].trim();
            if (tableId.length() == 0 || indexHint.length() == 0)
            {
                throwError("invalid parameters to useindex(table, index)");
            }
 
            // find actual table name minus id
            int dotIdx = tableId.indexOf('.');
            if (dotIdx < 0)
            {
                throwError("invalid table name in useindex(table, index)");
            }
 
            // find table name within declaration
            String tableName = tableId.substring(0, dotIdx);
            int tableIdx = sql.indexOf(" " + tableName + " ");
            if (tableIdx < 0)
            {
                throwError("unknown table name in useindex(table, index)");
            }
 
            // remove useindex function from predicate
            String predicate = sql.substring(endidx + 3);
            if (predicate.startsWith(" and ")) 
            { 
                predicate = predicate.substring(5); 
            }
 
            // inject use index after table declaration
            sql = sql.substring(0, tableIdx + 2 + tableName.length()) +
                  "use index (" + indexHint + ") " + 
                  sql.substring(tableIdx + 2 + tableName.length(), idx) +
                  predicate;
        }
 
        return sql;
    }
 
    protected void throwError(String message)
    {
        throw new IllegalStateException(message);
    }
}

There is quite a bit of logic there to go into depth, but the general idea is that it searches for each useindex(table, index)=1 declaration within the SQL statement. For each instance, it tracks back to the table declaration (via from or join) and appends the “use index(index)” statement to the table declaration. Finally, it removes the function from the where statement. So, going back to our example above, we end up with:

String hql1 = "select table from MyTable table where useindex(table, my_index) is true and table.feature is not null";
// before interceptor: select [omitted] from my_table table where useindex(table.id, my_index)=1 and table.feature is not null";
// after interceptor:  select [omitted] from my_table table use index (my_index) where table.feature is not null

Before that will actually work, though, we need to register the interceptor with Hibernate. There are multiple ways to do this depending on whether you are using straight Hibernate, Hibernate with Spring, JPA with Hibernate, etc. I will demonstate JPA with Hibernate via persistence.xml, but for other examples, see this article.

<property name="hibernate.ejb.interceptor" value="com.znet.hibernate.interceptor.IndexInterceptor" />

That’s it. You can now force the “use index” declaration within any HQL by simply adding useindex(table, index) is true to the where statement. Note that it should be the first statement in the where statement. In theory, although I have not tested this very much, the following complex example should work too:

String  hql = "select table from MyTable table " +
                     "inner join table.relative relative, " +
                    "OtherTable other " +
                     "where useindex(table,table_index) is true and " +
                     "useindex(relative,relative_index) is true and " +
                     "useindex(other, other_index) is true and " +
                     "relative.feature1 = other.feature2";

As I stated, this is just an example that I am using and I have in no way tested it to a large extent outside simple examples. If you do take an attempt to use it and find issues, feel free to post a comment and I will look into fixing or helping. Otherwise feel free to use this in any way that helps you.