[Firebird-devel] Performance questions

[Christian K. <ch....@sp...>]

Hi,

the database in my project is growing and I'm looking for places to 
improve the performance. For this I have the following question:

Is it worth to combine fields to get faster queries? For example I have 
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

the plan is:
PLAN JOIN (SR ORDER IX_SWIMRESULT_STYLE,M INDEX (PK_MEET))

the index IX_SWIMRESULT_STYLE is:
CREATE ASC INDEX ON SWIMRESULT (COURSE, GENDER, STYLEID, SWIMTIME);

The idea is to combine COURSE, GENDER and STYLEID in one database field. 
For COURSE and GENDER one byte is enough and STYLEID two bytes are ok. 
So I could use a field COURSEGENDERSTYLE of type INTEGER (32bit).


Then the query would be:
select * from SWIMRESULT SR join MEET M on SR.MEETID = M.MEETID
where
   SR.COURSEGENDERSTYLE = 131336
   and M.ENDDATE >= '01.01.2006'
order by SR.COURSEGENDERSTYLE, SR.SWIMTIME


the index would be IX_SWIMRESULT_STYLE is:
CREATE ASC INDEX ON SWIMRESULT (COURSEGENDERSTYLE, SWIMTIME);



cu Christian