You can use an index partially, but only if that part is the prefix of the rest of the index. As ID_PARAM is the second part of the index, the index cannot be used. Either reverse the columns in the index definition, or create a separate index. Note that given the name, I'd guess this should be a foreign key, which is automatically backed by an index.
Mark ----- Reply message ----- Van: "[email protected] [firebird-support]" <[email protected]> Aan: <[email protected]> Onderwerp: [firebird-support] Is it possible to use only "part" of the index? Datum: vr, jul. 17, 2015 16:14 Hello guys, today I've stumbled upon problem with performance. I have such table: CREATE TABLE DETAILS_DATA ( ID_MASTER INTEGER NOT NULL, ID_PARAM INTEGER NOT NULL, PARAM_VALUE VARCHAR(64) NOT NULL ); ALTER TABLE DETAILS_DATA ADD CONSTRAINT FK_DETAILS_DATA_ID_MASTER FOREIGN KEY (ID_MASTER) REFERENCES MASTER_DATA (ID); CREATE UNIQUE INDEX UNQ_DETAILS_DATA ON DETAILS_DATA (ID_MASTER,ID_PARAM); when performing this query: SELECT DD.PARAM_VALUE FROM DETAILS_DATA DD WHERE DD.ID_PARAM = 60 only the NATURAL plan is used. I thought that index UNQ_DETAILS_DATA will be used. I've tried to enforce a specific plan: SELECT DD.PARAM_VALUE FROM DETAILS_DATA DD WHERE DD.ID_PARAM = 60 PLAN (DD INDEX(UNQ_DETAILS_DATA)) But I've got message "index UNQ_DETAILS_DATA cannot be used in the specified pl an". FInally I did silly thing and joined this table with master table: SELECT DD.PARAM_VALUE FROM DETAILS_DATA DD INNER JOIN MASTER_DATA MD ON DD.ID_MASTER = MD.ID WHERE DD.ID_PARAM = 60 And now index is used and the whole query is working 20 times faster. Do I really need to include MASTER_DATA table to be able to use UNQ_DETAILS_DATA index? Thank you.
