1 comment inline. >-----Original Message----- >From: Michael Segel [mailto:[email protected]] On Behalf Of >[email protected] >Sent: Tuesday, September 15, 2009 8:19 AM >To: 'Derby Discussion' >Subject: RE: SELECT query takes 5 secs, what can I do? > >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.
Also, you can try to create a compound index on *only* the columns that you are selecting (instead of selecting *). Then, the DB should not have to open the table, it can just find all the information in the index. I don't know if Derby has this optimization, but it might be worth checking. >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
