Hi, a compound index is for nothing !
This task is simple to describe, but hard to realize.
The problem is because you are interesting in all
record from interval and in this case you can
use only one part of coumpond index.
Because live is realy long, you can't use some simplified
algoritmus and you must use this one:

Create next two table:
1. Time segments (one record for one day)
2. Assigning table (assign time segment to person)

All modification of tables will be done in triggers on person.

Finally you will ask time segment table for date, and count related
record in assigning table.

Also if you need only counts, you can pre counting in time segment table.

Slavek

Ing. Slavomir Skopalik
Jednatel spolecnosti
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
--------------------------------------------
Mobil: +420 724 207 851
icq:199 118 333
e-mail:skopalik@...
http://www.elektlabs.cz <http://www.elektlabs.cz/>



-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of unordained
Sent: Sunday, June 22, 2008 4:35 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Optimal algorithm for inventory




create index ix_lifetime on person (deathdate, birthdate);

Also, that 'or' could be causing some problems for optimizing the index
usage, you might try
breaking it up into two searches (if you weren't doing a straight 'count',
you could use this same
technique with a union instead):

select
/* this one could benefit from a compound index on (BirthDate, DeathDate) or
(DeathDate,
BirthDate) */
(select count(*) from person where BirthDate <= :EndDate and DeathDate >
:EndDate)



<http://groups.yahoo.com/;_ylc=X3oDMTJkdmt0OHRwBF9TAzk3MzU5NzE0BGdycElkAzI0N
DI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMjE0MTA1Njc5>
Yahoo! Groups
Change
<http://groups.yahoo.com/group/firebird-support/join;_ylc=X3oDMTJmamUxMmFjBF
9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA3
N0bmdzBHN0aW1lAzEyMTQxMDU2Nzk-> settings via the Web (Yahoo! ID required)
Change settings via email: Switch
<mailto:firebird-support-digest@yahoogroups.com?subject=Email Delivery:
Digest> delivery to Daily Digest | Switch
<mailto:firebird-support-traditional@yahoogroups.com?subject=Change Delivery
Format: Traditional> format to Traditional
Visit
<http://groups.yahoo.com/group/firebird-support;_ylc=X3oDMTJkMGlwczVhBF9TAzk
3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2hwZgR
zdGltZQMxMjE0MTA1Njc5> Your Group | Yahoo! Groups
<http://docs.yahoo.com/info/terms/> Terms of Use | Unsubscribe
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=>
.

<http://geo.yahoo.com/serv?s=97359714/grpId=2442406/grpspId=1705115386/msgId
=95141/stime=1214105679/nc1=4990217/nc2=5170416/nc3=5379228>




[Non-text portions of this message have been removed]