db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Performance issue of derby using JDBC
Date Wed, 17 Dec 2008 14:58:48 GMT
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.


> -Mike
>> -----Original Message-----
>> From: news [mailto:news@ger.gmane.org] On Behalf Of Harshad
>> Sent: Tuesday, December 16, 2008 11:20 PM
>> To: derby-user@db.apache.org
>> Subject: Re: Performance issue of derby using JDBC
[ snip ]

View raw message