Dear ./heLen!

Thank you for the explanation.

One more question though: is there a difference between primary keys or "normal" indexes? Do the rules you wrote apply also to primary compound keys?

-Zd

----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Friday, November 19, 2010 7:44 PM
Subject: Re: [firebird-support] Where to put my indexes?



At 06:56 AM 20/11/2010, Zd wrote:
>I'm bringing this to a new thread because it remained unanswered in the other.
>
>Let's say I have this table
>
>TABLE Calendar
>EventID Integer Primary Key (auto-incremented)
>UserID Integer
>StartTime TimeStamp
>EndTime TimeStamp
>Description VarChar(1000)
>etc.
>
>I query this table in two ways:
>
>A) SELECT StartTime, EndTime, Description, etc. FROM Calendar WHERE EventID = ...

Ascending index already exists as it is the PK. Any fields in the table that have been defined as Foreign Key will also have an existing ASC index. Don't duplicate constraint indexes.

>B) SELECT StartTime, EndTime, Description, etc. FROM Calendar WHERE UserID = ... AND StartTime <= ... AND EndTime >= ...

Separate indexes for StartTime and EndTime.

>Please note that UserID would have very low index selectivity (around 0).

Usefulness (or not) of an index on UserID will be proven by experimenting both with and without one.

>Where do I put my indexes for the best performance?

The rule of thumb is to index columns that are going to be searched on, joined on, or involved in sorts (ORDER BY, GROUP BY). However, nothing beats testing with typical data.

>Should I create a compound index on UserID, StartTime, EndTime? Or StartTime, EndTime, UserID?

No. Compound indexes are not (generally) useful in Firebird. Sometimes, a compound index can help a sort - but again, experiment with typical data to decide.

>Does the order matter? Do I need a compound index at all? What's the best way?

Avoid compound indexes unless you know from testing that a particular compound index helps. The optimizer will select any single-key index and use it for a multi-key search if it needs to.

Remember that only the leftmost fields (keys) of a compound index are available, as and when the search keys in the WHERE clause match by order and type-compatibility. The left-to-right order of the keys will matter for a sort on multiple keys, i.e., the ORDER BY or GROUP BY list should be in the same l-to-r order as the index keys.

On the other side, the engine can use the first key of a compound index as though it were a single-key index. Usually this is not as useful as having the single-key index, but it might be useful if you have a query that "likes" the multi-key index for the sort specification and is also searching records on the first key.

Don't make it a "design rule" to index everything on principle. Start with the essential, single-key indexes that your requirements tell you are certain to be search keys. Other indexes can be tested later, when you have good sets of typical data, typical multi-user loads and a need to improve the performance of certain queries.

./heLen





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