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 Yay!
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: > [email protected] 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,
