sasidhardoc wrote:

> Adam said:
>> If you have a foreign key constraint on
> LINK_MEDICALFACILITY_PATIENT.MEDICALFACILITY_ID, than the underlying
> index will be used. If your database contains very few MEDICALFACILITY
> records, this index may be worthless or even counter productive.
>
> The table LINK_MEDICALFACILITY_PATIENT.MEDICALFACILITY_ID has three
> fields. LINK_ID, MEDICALFACILITY_ID and PATIENT_ID. The latter two
> have foreign key constraints and the first is the PK.

Primary and foreign keys are automatically indexed.

> The field MEDICALFACILITY_ID has many duplicates.
>So, I created a new index on this table:
> CREATE UNIQUE INDEX IDX_LINK_MED_PAT ON
> LINK_MEDICALFACILITY_PATIENT(MEDICALFACILITY_ID,LINK_ID);

Indexes are most useful if they are selective, they're normally useless
with lots of duplicates (i.e. it is only in very rare cases that there
is any point in indexing a field representing a boolean value).

Never create duplicate indexes, i.e. if there already is an index or key
which starts with this field, then don't create any (duplicate,
confusing to the optimizer) index. Though you're right in that if you
didn't have a foreign key on that field and decided to index it, then it
would be better (for updates and deletes) to add the primary key to the
end of the index.

> I backed up and restored the Db. Then, I ran the SP for
> MEDICALFACILITY_ID = 1 under 3 scenarios
> 500/100000 records have MEDICALFACILITY_ID = 1
> 50000/100000 records have MEDICALFACILITY_ID = 1
> 90000/100000 records have MEDICALFACILITY_ID = 1
> In all cases, using the new index, or not using it makes no
> difference. The plan is:

> PLAN MERGE (SORT (LINK_MEDICALFACILITY_PATIENT INDEX
> (IDX_LINK_MEDIC_PAT)), SORT (JOIN (ENTY_PERSON INDEX
> (I_ENTY_PERSON_LASTNAME), ENTY_PATIENT INDEX (I_ENTY_PATIENT_ENTITY_ID))))
> Why does this index not make any difference? TIA

So, this plan is caused by this query?

> SELECT ENTY_PATIENT.PATIENT_ID, ENTY_PERSON.PERSON_LASTNAME || ', ' ||
> ENTY_PERSON.PERSON_FIRSTNAME
> FROM LINK_MEDICALFACILITY_PATIENT LMP
> JOIN ENTY_PATIENT EPA1 ON (LMP.PATIENT_ID = EPA1.PATIENT_ID)
> JOIN ENTY_PERSON EPE ON (EPA1.ENTITY_ID = EPE.ENTITY_ID)
> WHERE LMP.MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN
> AND UPPER(EPE.PERSON_LASTNAME) STARTING WITH UPPER(:SEARCHSTRING_IN)

I'm surprised, I didn't think it was possible (short of running Firebird
2.0) to index 'UPPER(EPE.PERSON_LASTNAME)'. I'd rather expect something like

PLAN SORT( JOIN( JOIN( LINK_MEDICALFACILITY_PATIENT INDEX(
IDX_LINK_MEDIC_PAT), ENTY_PATIENT INDEX( I_ENTY_PATIENT_PATIENT_ID)),
ENTY_PERSON INDEX (I_ENTY_PERSON_ENTITY_ID)))

Do you have an active index/key on ENTY_PATIENT.PATIENT_ID, are you
running Firebird 2.0, is there something with Firebird 1.5 that I never
grasped or something I simply fail to see? (I'm not saying that my
expected index is any better, with 90% of the records having the same
value for a field, it is most likely not the ideal plan).

Maybe we can see the entire SP as well as how you call it?

Set