Hi,

I have the follwoing query:

select * from swimresult sr
join style st on st.styleid = sr.styleid
left join swimevent se on se.SWIMEVENTID = sr.SWIMEVENTID
where sr.MEETID = 94312 and st.stroke = 0
order by st.DISTANCE, se.round, sr.place, sr.SWIMTIME

the plan used for this query is:
PLAN SORT (JOIN (JOIN (SR INDEX (IX_SWIMRESULT_MEET),ST INDEX
(PK_STYLE)),SE INDEX (PK_SWIMEVENT)))

And the query runs at a good speed.

Now I join another table (ATHLETE) and the query looks like this:

select * from swimresult sr
join style st on st.styleid = sr.styleid
join athlete a on sr.ATHLETEID = a.ATHLETEID
left join swimevent se on se.SWIMEVENTID = sr.SWIMEVENTID
where sr.MEETID = 94312 and st.stroke = 0
order by st.DISTANCE, se.round, sr.place, sr.SWIMTIME

The plan changes to:
PLAN SORT (JOIN (JOIN (A NATURAL,SR INDEX
(IX_SWIMRESULT_MEET,IX_SWIMRESULT_ATHLETE),ST INDEX (PK_STYLE)),SE INDEX
(PK_SWIMEVENT)))

This is much slower and I'm looking for a way to tell Firebird, to use
the IX_SWIMRESULT_MEET index on SWIMRESULT.

I already recomputed all index selectivities. The DDL looks like this:

RECREATE TABLE ATHLETE /* 30'000 records */
(
ATHLETEID INTEGER NOT NULL,
CLUBID INTEGER,
BIRTHDATE TIMESTAMP,
FINAID INTEGER,
FIRSTNAME VARCHAR( 30) COLLATE ISO8859_1,
GENDER SMALLINT DEFAULT 0,
LASTNAME VARCHAR( 50) COLLATE ISO8859_1,
MIDDLENAME VARCHAR( 30) COLLATE ISO8859_1,
NATION VARCHAR( 3) COLLATE ISO8859_1,
CONSTRAINT PK_ATHLETE PRIMARY KEY (ATHLETEID)
);

RECREATE TABLE STYLE /* 50 records */
(
CANSELECT VARCHAR( 1) DEFAULT 'F' NOT NULL COLLATE
ISO8859_1,
DISTANCE SMALLINT,
NAME VARCHAR( 50) COLLATE ISO8859_1,
RELAYCOUNT SMALLINT,
STROKE SMALLINT,
STYLEID SMALLINT NOT NULL,
STYLESORT SMALLINT,
TECHNIQUE SMALLINT,
CONSTRAINT PK_STYLE PRIMARY KEY (STYLEID)
);

RECREATE TABLE SWIMEVENT
(
SWIMEVENTID INTEGER NOT NULL,
AGEMAX SMALLINT,
AGEMIN SMALLINT,
GENDER SMALLINT DEFAULT 0,
MEETID INTEGER,
NUMBER SMALLINT,
PREVEVENTID INTEGER,
ROUND SMALLINT,
SESSIONNUMBER SMALLINT,
STARTTIME TIMESTAMP,
STYLEID SMALLINT NOT NULL,
CONSTRAINT PK_SWIMEVENT PRIMARY KEY (SWIMEVENTID)
);

RECREATE TABLE SWIMRESULT /* 600'000 records */
(
SWIMRESULTID INTEGER NOT NULL,
ATHLETEID INTEGER NOT NULL,
COMMENT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
CLUBID INTEGER,
COURSE SMALLINT,
MEETID INTEGER,
POINTS SMALLINT,
PLACE SMALLINT,
REACTIONTIME SMALLINT,
STATUS SMALLINT,
STYLEID SMALLINT NOT NULL,
SWIMEVENTID INTEGER,
SWIMTIME INTEGER,
CONSTRAINT PK_SWIMRESULT PRIMARY KEY (SWIMRESULTID)
);

CREATE ASC INDEX IX_SWIMRESULT_ATHLETE ON SWIMRESULT (ATHLETEID,
STYLEID, COURSE, SWIMTIME);

CREATE ASC INDEX IX_SWIMRESULT_STYLE ON SWIMRESULT (COURSE, STYLEID,
SWIMTIME);

CREATE ASC INDEX IX_SWIMRESULT_MEET ON SWIMRESULT (MEETID, STYLEID);


Thanks for any help to my problem.

cu Christian