db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Harshad <harshad...@gmail.com>
Subject Re: Performance issue of derby using JDBC
Date Sat, 20 Dec 2008 08:46:57 GMT
Thanks Mike & Kristian for the tips on indexes.

Creating an index on multiple columns in my DB does indeed speed up the original query, so

It would have been even cooler if I could just declare that combination of two columns will
be unique, rather than create an index (with its associated overheads). (I am assuming that
in this instance the actual index is not useful to the optimiser)

Kristian Waagan wrote:

> derby@segel.com wrote:
>> Hi,
> [ snip ]
>> You can create as many indexes on a table as you want, provided that the
>> names of the indexes are unique. One major caveat... Too many indexes may
>> confuse the optimizer and when your query is processed, the engine may
>> choose a different index.
> In addition to what Mike is saying, you should also consider a few more
> issues regarding indexes.
> Indexes have to be maintained, which can hurt your performance for
> certain types of load (i.e. inserts and deletes).
> Another issue is that they take up storage space. In Derby each index is
> represented by a file on disk.
> Finally, I believe Derby still has an issue with keeping the index
> statistics updated (but an automatic solution is being worked on). If
> you're loading up your tables with lots of data, consider creating the
> indexes after the load or compress the tables to update the statistics.
> Regards,

View raw message