You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Andrew Bruno <an...@gmail.com> on 2009/09/15 10:07:02 UTC
SELECT query takes 5 secs, what can I do?
Hello,
I have a query that used to take 10secs to run, i.e.
select * from Message m where
m.messageId='<79...@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
RE: SELECT query takes 5 secs, what can I do?
Posted by Mike Baranski <li...@secmgmt.com>.
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 <an...@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='<79...@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
Re: SELECT query takes 5 secs, what can I do?
Posted by "dev@xx" <de...@proxiflex.fr>.
>Can you 'detatch' your index? By this I mean store the index on a different
>disk?
Is this possible with Derby ? I did look for this feature but didn't find a
way to do it ...
I found that log could be set on different device but not index...
JY Linet
----- Original Message -----
From: "Michael Segel" <ms...@segel.com>
To: "'Derby Discussion'" <de...@db.apache.org>
Sent: Tuesday, September 15, 2009 2:21 PM
Subject: RE: SELECT query takes 5 secs, what can I do?
> This is also true.
>
> Can you 'detatch' your index? By this I mean store the index on a
> different
> disk?
>
> Also what type/size machine are you running your query on? How much
> memory?
> What else is in the table? Are the rows fat?
>
> HTH to point you down a possible decision path.
>
> -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 <an...@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='<79...@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
>
>
RE: SELECT query takes 5 secs, what can I do?
Posted by Michael Segel <ms...@segel.com>.
This is also true.
Can you 'detatch' your index? By this I mean store the index on a different
disk?
Also what type/size machine are you running your query on? How much memory?
What else is in the table? Are the rows fat?
HTH to point you down a possible decision path.
-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 <an...@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='<79...@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
RE: SELECT query takes 5 secs, what can I do?
Posted by de...@segel.com.
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 <an...@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='<79...@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
Re: SELECT query takes 5 secs, what can I do?
Posted by Brett Wooldridge <br...@gmail.com>.
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 <an...@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='<79...@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
Re: SELECT query takes 5 secs, what can I do?
Posted by Rick Hillegas <Ri...@Sun.COM>.
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='<79...@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
>