db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Baranski" <list-subscripti...@secmgmt.com>
Subject RE: SELECT query takes 5 secs, what can I do?
Date Tue, 15 Sep 2009 12:37:50 GMT
1 comment inline.

>-----Original Message-----
>From: Michael Segel [mailto:msegel@segel.com] On Behalf Of
>derby@segel.com
>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: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