Thank you all for the responses. The db runs as part of an applicatioon that sits on a windows box, and we have many installed version around the world, so we don't control hardware,etc.. It only has a few db connections, and at the momement this particular instancee is looping through all the messages, and seeing whether they have been archived in the cloud yet... And as you can imagine, at 5secs a message its taking a long time.
The message table has a bunch of other columns, and since I am using hibernate I need all columns... But I wonder if it would be quicker to select just the id column, and then load/select all columns using where id=.. Sounds crazy.. Kinda like 1+N selects...But any thoughts? As for the compund index, I thought it was not possible to add a pk index I.e. Id as part of a compound, but either way I will try. Also, if I do a count(messageid) instead, are there any other optimization tricks? Keep all thoughts coming, crazy or sound :) Appreciated Andrew -----Original Message----- From: Rick Hillegas <[email protected]> Sent: Tuesday, 15 September 2009 10:32 PM To: Derby Discussion <[email protected]> Subject: Re: SELECT query takes 5 secs, what can I do? Hi Andrew, You might try adding more columns to your index so that it covers the whole WHERE clause: CREATE INDEX IDX_Message_MessageId ON ExchangeSync.Message (messageId, archived, id) Hope this helps, -Rick Andrew Bruno wrote: > Hello, > > I have a query that used to take 10secs to run, i.e. > > select * from Message m where > m.messageId='<[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 >
