db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: SELECT query takes 5 secs, what can I do?
Date Tue, 15 Sep 2009 12:18:35 GMT
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:brett.wooldridge@gmail.com]
> 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 <andrew.bruno@gmail.com> wrote:
> 
> > Hello,
> >
> > I have a query that used to take 10secs to run, i.e.
> >
> > select * from Message m where
> > m.m
> > essageId='<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


Mime
View raw message