Raible Designs

Creating column indexes with Hibernate

One of the best ways to speed up your application's performance is to create or optimize indexes in your database. On my current project, when we created our database on the AS/400 last week, the DBA noticed that there weren't any indexed created. I expected this and said I'd do some research on creating indexes with Hibernate. Thanks to Gavin, it turns out to be quite simple. Let's say you have an XDoclet tag on a column you want to index. Currently it is:

@hibernate.property column="username" not-null="true"

If you're using Hibernate's <schemaexport> task, you can add an index on this column to your mapping file and it'll create the index when creating the database. To add an index, it's as simple as changing the above XDoclet tag to:

@hibernate.property
@hibernate.column name="username" not-null="true" index="index_name"

Now the hard part comes. Which columns should you put indexes on? From what I've heard, it's the ones that you use in where clauses of your queries. I expect one or two per table is sufficient (??). One thing I'm not sure of is: should id columns contain an index?

Posted in Java at May 03 2004, 12:06:44 PM MDT 11 Comments

Comments:

:-) Choosing your indexes is always the hard part, and usually requires some usage monitoring to see where your bottlenecks are showing up. All Primary and Foreign key fields should automatically be indexed by your database.

Posted by Jason Carreira on May 03, 2004 at 06:11 PM MDT #

Based on the username column, I'm assuming you've created a unique index. Do you know the syntax for a nonunique index, or a multicolumn index? One other rule of thumb is to consider the usage of the database. If the system has heavy reporting with infrequent inserts or updates, you would be fairly aggressive in choosing indexes. If you have a system heavy on transactions and light on reporting, you'd be conservative when choosing columns to index. You can also have a column be part of several indicies. In a large db of people, you might index the lastname column (nonunique). You might also index the combination of state born in, lastname, firstname.

Posted by Ted on May 03, 2004 at 06:54 PM MDT #

Matt, I think it is going to largely depend on the type of queries you are running against your data. I definitely would HIGHLY RECOMMEND you go out and buy SQL Tuning. It covers how to disect your queries and uses the author's own techique for finding the optinum query. By knowing the query's exceution plan and driving tables you can decide which indexes you need to use since you will know exactly how they are going to be run by the database.

Posted by dsuspense on May 03, 2004 at 08:13 PM MDT #

If you are using DB2 8.x, which most probably aren't, you can use db2advis to generate indexes based on a group of sql statements.

Posted by Mark on May 03, 2004 at 08:31 PM MDT #

I recently had to analyse a Kodo JDO generated query, and thanks to the SQL Tuning book I was able to derive the execution plan by hand and more understand how the query works. It was a 6 table join query with a where clause containing 5 AND-ed together ranged filters. The driving table is always your key to the performance of your query, since this is where all data retrieval will pivot off of. The filters and WHERE clauses normally do not have as much an effect on the query performance as the driving table and join method selected by the database when joining tables in a query. Most databases and ORM tools (JDO and Hibernate) should generate fairly good SQL from your JDOQL or Hibernate QL. But look closely at the SQL and see what it generates, it may be eye opening. Normally just copy-past them into your query executor and take a look at the plans. Also, since you have DBAs on hand, you can have them analyse the queries and see what their recomended indexing would be, that is normally something they are very familiar with.

Posted by dsuspense on May 03, 2004 at 08:39 PM MDT #

On the AS/400 (iSeries) use the SQL Monitor in Client Access for the iSeries to do index tuning. It will tell you where you need indexes. The iSeries SQL Optimizer, which runs when you use SQL, will tell you which access path (index) was used or built to run your queries. This is shown by the monitor.

Posted by Raymond Phillips on May 04, 2004 at 09:09 PM MDT #

Hai This is harun.I am using Spring framework,Hibernate,Appfuse. I am getting an error "org.springframework.beans.NullValueInNestedPathException: Invalid property 'game' of bean class [com.lottory.model.Scheme]: Value of nested property 'game' is null" I am using game table & scheme table. In game table id is the primary key in scheme table i am using game id as a foreign key. Tell me the solution

Posted by Harun on January 24, 2005 at 07:59 AM MST #

Multi-column indexes are possible: http://forum.hibernate.org/viewtopic.php?t=926694&highlight=index+multicolumn but I haven't done a full test yet. Basically, hibernates auto ddl will pick up that you have used the same index name for multiple columns and make the index multicolumn. I think!

Posted by James Shepherd on October 26, 2005 at 09:56 PM MDT #

= mgh0m08m0 l;]ll8lu

Posted by 202.56.245.58 on November 29, 2007 at 10:24 AM MST #

uuuuuh... And primary keys are always indexed...

Posted by Lego Programmer on April 23, 2008 at 10:26 AM MDT #

xhbsxfdfchbdf

Posted by 59.177.70.74 on March 24, 2009 at 01:46 PM MDT #

10+ YEARS

TWITTER

RECENT ENTRIES

Tag Cloud

Creating column indexes with Hibernate