Adding database indexes with Hibernate

I recently had to add additional indexes to some tables in a MySQL database that were setup with Hibernate.

A simple task… in the mapping hbm.xml just specify the index on the property by adding:


index="some_index_name"

Except it didn’t work. Even dropping the tables and letting Hibernate re-create them from scratch with the updated mappings didn’t work. As it turns out, there is a bug in the implementation of Hibernate with MySQL that doesn’t create the indexes.

The workaround is to setup a database-object block in your Hibernate mapping file with the SQL you need:

<hibernate-mapping>
<database-object>
<create>
CREATE INDEX my_index ON my_table (column_name)
</create>
<drop></drop>
</database-object>
</hibernate-mapping>

You can also specify which dialects you want this targetted at as well:

<hibernate-mapping>
<database-object>
<create>
CREATE INDEX my_index ON my_table (column_name)
</create>
<drop></drop>
<dialect-scope name=”org.hibernate.dialect.Oracle9Dialect”/>
<dialect-scope name=”org.hibernate.dialect.OracleDialect”/>
</database-object>
</hibernate-mapping>

Hopefully this will save someone some time!

References:

Advertisements

7 thoughts on “Adding database indexes with Hibernate

    • Yeah it’s hit and miss and I don’t know why. I believe the gradebook tool uses them successfully though? I never bothered to investigate further and just ran some SQL to create the indexes I needed.

    • You probably want to create the indexes before you load the data so that any constraints are enforced when loading the data. But doing afterwards is ok too.
      You would just do it as some DDL. I now use Spring JDBC for the majority of my Java DB work so doing this in Java would be as simple as running the appropriate create index or constraint syntax via the JDBC template.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s