This is also true. Can you 'detatch' your index? By this I mean store the index on a different disk?
Also what type/size machine are you running your query on? How much memory? What else is in the table? Are the rows fat? HTH to point you down a possible decision path. -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
