Mysql index hints in Hibernate query

Question

How can we specify Mysql index hints in Hibernate query ?

Using index hints in Mysql queries becomes imperative in cases where query planner doesn't choose right indexes (ref: https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/).

This can be easily done through raw query as specified here: https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

Answer 1

In Hibernate 5 you can define query hints to the underlying database using org.hibernate.query.Query.html#addQueryHint. The SQL dialect being used is responsible to inject the appropriate text related to each hint defined in the query.

Unfortunately, if you read the sources of org.hibernate.dialect.MySQLDialect you'll notice that it does not extends the method org.hibernate.dialect.Dialect#getQueryHintString, and it does nothing by default.

You can try finding some alternative MySQL dialect that implements database query hinting about indexes or, if you're willing to get your hands dirty, you can extend one of the MySQL dialects and do it yourself:

public class MySqlExtendedDialect extends MySQLDialect {

    public String getQueryHintString(String query, List<String> hints) {
       /** define a format and read the hints, then patch the query appropriately **/
        String modifiedQuery = query;
        return modifiedQuery;
    }

}

You can, for example, define a hint like "USE_INDEX_COL1_COL2" in the query. Then when your dialect find a "USE_INDEX_*" hint, it needs to do some basic SQL parsing and inject the right text in the right place.

Pure fun !

Answer 2

I couldn't get this approach working, the "getQueryHintString" method was not called.

Followed now the solution described here:

How to insert an "Optimizer hint" to Hibernate criteria api query

with implementing a Hibernate Interceptor which worked fine.

Answer 3

As of Hibernate 5.2.12 query hinting is now supported for MySQL as well using the org.hibernate.query.Query.html#addQueryHint method.