db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Bruno <andrew.br...@gmail.com>
Subject RE: SELECT query takes 5 secs, what can I do?
Date Tue, 15 Sep 2009 13:02:16 GMT
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 :)


-----Original Message-----
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Sent: Tuesday, 15 September 2009 10:32 PM
To: Derby Discussion <derby-user@db.apache.org>
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,

Andrew Bruno wrote:
> Hello,
> I have a query that used to take 10secs to run, i.e.
> select * from Message m where
> m.messageId='<7997716ED1AF3D47A35D74FA2CB610920255303F@somedomain.com>'
> 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

View raw message