When we design our metadata, there are a few approaches we can take:
Create a single index for only one of the columns. If there are only 10 records in the table for any given date, for example, then a single index on FOO_DATE is enough to allow us to find any record in the table quickly.
Create one index for each column in the WHERE clause (three total). InterBase will combine the indices when the
query is run.
Create a single index on all three columns (one index total).
If solution (1) works for your particular application, great. If not, we need to examine (2) and (3).
Let's imagine you choose solution (2), and create indices on FOO_DATE, FOO_COLOR, and FOO_DSC. When you type a WHERE clause like the one above, the InterBase optimizer will notice that all three indices could be useful in performing the
query.
It makes a kind of bitmap of each index, where a bit is turned on if there's an index node for a particular value, and turned off if there isn't. It then combines the bitmaps using a binary AND operation, and uses the resulting bitmap as a single index into the table. There is some overhead in doing this, but it isn't too bad in most cases.
If you choose solution (3), then InterBase can use your multi-column index as-is, without any combination of separate indices. So (3) may produce the fastest
query results for this particular SELECT. But what about other statements; will the index still be useful?