>What??? In this case I misunderstand the role of indexes in the
>optimisation. If I have 2 Fields: F1 and F2, and I need a query:
>SELECT * FROM MyTable WHERE F1 = 'X' and F2 = 'Y', do I need a
>compound index on (F1, F2)? Till now, I thought I need an index on F1
>_and_ an index on F2. Consider F1 is the primary key, then I have to
>duplicate the key in the compound index?
>
>PLEASE, can anybody confirm this? Am I on the wrong way?

Well, don't take my word for it. I'm pretty sure that it does have some
effect on speed, but I doubt a compound index will always be better than
single indexes (I work on only a few tables most of my time, and have too
little experience to draw solid conclusions).

The only general advice I've gathered, is that you want your indexes to not
contain too many duplicate values (e.g. if you index a field named country,
you could have an awful lot of persons living in China), then add the PK at
the end of that index. I think the reason is that deletes (and possibly
inserts) slow down with many duplicates.

One thing I suddenly realised: Your PK is (Work_Day, Emp_No). That means
that IB has to look up every individual date (since you use BETWEEN and is
interested in more than one date) and then try to find the employee. If
your PK was the other way around (Emp_No, Work_Day), then IB could just
locate the employee once find the first work_day and then traverse down the
work_days (since they would be in sequence). I think that would be worth a
try.

HTH,
Set