Christian Kaufmann wrote:

>
> Is it worth to combine fields to get faster queries?

Generally, no. First, one of the rules that makes relational
databases flexible is that each field contains an atomic data
item. Combining fields makes it difficult and expensive to
ask questions parts of the combined field.

Second although this query:

>
> select * from SWIMRESULT SR join MEET M on SR.MEETID = M.MEETID
> where SR.STYLEID = 8 and SR.COURSE = 2 and SR.GENDER = 1
> and M.ENDDATE >= '01.01.2006'
> order by
> SR.COURSE, SR.GENDER, SR.STYLEID, SR.SWIMTIME

produces this plan:

> PLAN JOIN (SR ORDER IX_SWIMRESULT_STYLE,M INDEX (PK_MEET))

Firebird will actually use the indexes on STYLEID, COURSE
and GENDER - assuming they are reasonably selective. The
plan reports only the primary index used to establish the
order of joins for each table. Other indexes will be added
if the optimizer decides they're useful.

However, in the case in point, you probably have only two
distinct values for GENDER, and no more than eight for COURSE.
In a large table, indexes on those fields will have poor
selectivity. So, a combined field might work. Test it on
a large database before you commit yourself to a plan that
makes the data harder to use and understand.


Regards,


Ann