Sorry to top post, just rushing out the door. Adding a second index most likely will not help. If JavaDB/Derby/Cloudscape is anything like most other RDBMSes, you will only filter your selection set on one index per table. So since you're selecting from one table, you can only use one index.
(I'm sure someone from IBM or Sun will correct me if I'm wrong. ;-) I would suggest if you know that you usually use a specific field as a second filter clause that you consider creating a compound index. (Index that contains multiple columns for uniqueness.) In your select statement below, I'm guessing that archived is a binary field, but I don't know how unique the id is. I would suggest that you create a single index based on messageId and then id. However... I don't know how, if any performance will be gained. The issue is that your messageId appears to be unique so that by itself you should return a single row. (If this is true, then why do you have the second and third filter?) HTH -Mikey > -----Original Message----- > From: Brett Wooldridge [mailto:[email protected]] > Sent: Tuesday, September 15, 2009 4:37 AM > To: Derby Discussion > Subject: Re: SELECT query takes 5 secs, what can I do? > > Consider making a separate archive table and live table. Looking at > that query, I doubt any other index will help. > > Sent from my iPhone > > On Sep 15, 2009, at 17:07, Andrew Bruno <[email protected]> wrote: > > > Hello, > > > > I have a query that used to take 10secs to run, i.e. > > > > select * from Message m where > > m.m > > essageId='<[email protected]>' > > and m.id != 933927 and m.archived=1 > > > > The Message table has around one million rows. > > > > I added the following index > > > > CREATE INDEX IDX_Message_MessageId ON ExchangeSync.Message (messageId) > > > > and now it takes 5secs. > > > > Is there anything else I can do? > > > > Should I add an index on the boolean "archived" column too? > > > > Any performance hints appreciated. > > > > Thanks > > Andrew
