--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:57 AM 10/11/2003 +0000, you wrote:
> > > > I have a table called history with the below attributes:
> > > > IPAddress int ,
> > > > RowId int ,
> > > > DateTime timestamp ,
> > > > RawSec int ,
> > > > RawUSec int ,
> > > > SeqSec int ,
> > > > SeqUSec int ,
> > > > CurrValue float ,
> > > > PresentState smallint ,
> > > > DataQuality int ,
> > > > FlagManuallySet char(1) not

null,

> > > > FlagBlocked char(1) not

null,

> > > > FlagControlInhibit char(1) not

null,

> > > > FlagTelemFailed char(1) not

null,

> > > > FlagTestMode char(1) not

null,

> > > > FlagCalcFailure char(1) not

null,

> > > > FlagLinkFail char(1) not

null,

> > > > FlagPointFault char(1) not

null,

> > > > FlagUnreasonable char(1) not null
> > > >
> > > > Currently this table has about 22 million of records. The

index

> >has
> > > > been created on IPAddress, Rowid and DateTime. When I select

from

> > > > this table, I encountered performance problem (as user

complained

> > > > about it). I have no idea how to improve the performance. Can
> >anyone
> > > > help ?
> >
> >Can you advise me what are the information you need ?
> >
> >I always select with condition such as "where IPAddress = 123 and
> >RowId = 345 and DateTime >= '2003-10-1 00:00:00' and DateTime

< '2003-

> >10-2 00:00:00'". and I create index key on IPAddress, RowId and
> >DateTime.
> >
> >Is the above information help ?
>
> Getting there.
>
> What is the primary key of this table?
>
> If the primary key has the same segments as your index, do you

therefore

> have *both* a primary and an index on this group?
>
> What kind of performance problem does your user complain about?
>
> What does the query plan look like?
>
> Do you have any other indexes on this table?
>
> heLen

There is no primary key in this table. Indexes are:
1) history_idx = IPAddress, RowId, DateTime
2) history_idx2 = datetime
3) history_idx3 = rawsec, rawusec (plan to remove it in future).

how do I check the segments for index ?

Here is the query plan:
for example this query that took about 10 sec to execute the below
statement:
select * from history where ipaddress = -1056765951 and rowid =
28844063 and datetime >= '2003-10-1 00:00:00' and datetime < '2003-10-
2 00:00:00'

PLAN (HISTORY INDEX (HISTORY_IDX,HISTORY_IDX2))

Any way to improve it ?

rgds