Index Hints (FORCE INDEX)

[adamu]

Hello. I would like to use Mysql's index hints, like this (simplified):

SELECT * FROM my_table FORCE INDEX(PRIMARY)...

Is there a way to do this with Exposed? I tried reading through #118 to see if it was possible, but that seems to be talking about executing fully custom SQL. I just want to add an index hint to an existing Exposed query chain.

Thanks.

[adamu]

The correct way is probably to add this functionality to Query, but I've worked around this for now by overriding Table.describe() in my table object, which causes FORCE INDEX to be injected here:

override fun describe(s: Transaction, queryBuilder: QueryBuilder) {
    super.describe(s, queryBuilder)
    if (queryBuilder.toString().startsWith("SELECT")) {
        queryBuilder.append(" FORCE INDEX(PRIMARY) ")
    }
}

Obviously this is not a long-term solution.

[bog-walk]

Using MySQL-specific index hints will become possible in upcoming version 0.53.0 which opens the Query class for customization. An example of how to take advantage of the now public copyTo(), by creating a custom Query subclass for these index hints, has been added to the site documentation, which will be synced as part of the upcoming release.