Hi

I test firebird 2.5.1 an found that Statements with IN or EXISTS don't use an Index, example:

SELECT * FROM ARTICLE_CONTENTTREE_LINK WHERE ARTICLEID in (Select RELID FROM ARTICLE WHERE DEFINEID=112435)

OR

SELECT * FROM ARTICLE_CONTENTTREE_LINK WHERE EXISTS
(Select RELID FROM ARTICLE WHERE DEFINEID=112435 AND ARTICLE.RELID=ARTICLE_CONTENTTREE_LINK.ARTICLEID)

Plan:
------------------------------------------------------------------------------
PLAN (ARTICLE INDEX (RDB$PRIMARY45))
PLAN (ARTICLE_CONTENTTREE_LINK NATURAL)

Adapted Plan
PLAN (ARTICLE INDEX (PK_ARTICLE)) PLAN (ARTICLE_CONTENTTREE_LINK NATURAL)
------------------------------------------------------------------------------

In Firebird 1.5 I know this typ of Query are not optimized correctly... in Firebird 2.1 I remember this problem are solved???

Know I test Firebird 2.5.1 and found that this problem still there... or again there... or is this an mistake from me ?

The table structur:

------------------------------------------------------------------------------
/* Table: ARTICLE */

CREATE TABLE ARTICLE (
RELID INTEGER NOT NULL,
USERID INTEGER NOT NULL,
DEFINEID INTEGER,
CHANGED TIMESTAMP,
DATING TIMESTAMP,
TITLE BLOB sub_type 1 segment size 64,
PTITLE MINICMS_TITLE,
WTITLE MINICMS_TITLE,
TEASER BLOB sub_type 1 segment size 128,
PTEASER BLOB sub_type 1 segment size 128,
BODY BLOB sub_type 1 segment size 256,
PBODY BLOB sub_type 1 segment size 256,
ENABLED SMALLINT DEFAULT 1,
DELETED SMALLINT,
VALID_FROM TIMESTAMP,
VALID_TO TIMESTAMP,
SOURCE BLOB sub_type 1 segment size 64,
AUTHOR BLOB sub_type 1 segment size 64,
KEYWORDS BLOB sub_type 1 segment size 64,
NOTICE BLOB sub_type 1 segment size 256,
MAILING SMALLINT DEFAULT 0,
PROTECTLEVEL SMALLINT DEFAULT 10,
TYP SMALLINT DEFAULT 0,
URL BLOB sub_type 1 segment size 32,
EMAIL BLOB sub_type 1 segment size 32,
WEBTIP SMALLINT DEFAULT 0,
DATE_BEGIN TIMESTAMP,
DATE_END TIMESTAMP,
POSTCODE VARCHAR (8) character set ISO8859_1 collate ISO8859_1,
CITY VARCHAR (128) character set ISO8859_1 collate ISO8859_1,
COUNTRY SMALLINT,
NATION SMALLINT,
PRICEGROUP SMALLINT DEFAULT 0,
PRICEGROUP_ABO SMALLINT DEFAULT 0,
PRICEGROUP_ABO_FREE SMALLINT DEFAULT 0,
PUBLICITY_LINK INTEGER,
PARAMS BLOB sub_type 1 segment size 64,
QUERY_TITLE VARCHAR (128) character set ISO8859_1 collate ISO8859_1,
QUERY_TEXT BLOB sub_type 1 segment size 256,
EXEMPTION_LIMIT SMALLINT,
ROAYLTY_SHARE DOUBLE PRECISION,
QUERY_LOCAL MINICMS_QUERY_TEXT,
XMLDATA MINICMS_XMLDATA,
CALC_PUBLICITY_ARTICLE COMPUTED BY (CAST ( CASE WHEN (PUBLICITY_LINK Is NULL) THEN 2 ELSE 1 END AS SMALLINT )),
KEY_INT1 INTEGER,
KEY_INT2 INTEGER,
KEY_INT3 INTEGER,
KEY_FLOAT1 DOUBLE PRECISION,
KEY_FLOAT2 DOUBLE PRECISION,
KEY_FLOAT3 DOUBLE PRECISION);



/* Primary keys definition */

ALTER TABLE ARTICLE ADD CONSTRAINT PK_ARTICLE PRIMARY KEY (RELID);


/* Foreign keys definition */

ALTER TABLE ARTICLE ADD CONSTRAINT FK_ARTICLE_PUBLICITY_LINK FOREIGN KEY (PUBLICITY_LINK) REFERENCES ARTICLE (RELID) ON DELETE SET NULL ON UPDATE SET NULL;


/* Indices definition */

CREATE INDEX FK_ARTICLE_PUBLICITY_LINK ON ARTICLE (PUBLICITY_LINK);
CREATE INDEX IDX_ARTICLE_DEFINEID ON ARTICLE (DEFINEID);
CREATE INDEX IDX_ARTICLE_MAILING ON ARTICLE (ENABLED, MAILING, DATING, VALID_FROM, VALID_TO);
CREATE INDEX IDX_ARTICLE_USERID ON ARTICLE (USERID);

--------------------------------------------------------------------------

/* Table: ARTICLE_CONTENTTREE_LINK */

CREATE TABLE ARTICLE_CONTENTTREE_LINK (
TREEID INTEGER NOT NULL,
ARTICLEID INTEGER NOT NULL,
SORTKEY INTEGER,
PARAMS MINICMS_PARAMS);



/* Primary keys definition */

ALTER TABLE ARTICLE_CONTENTTREE_LINK ADD CONSTRAINT PK_ARTICLE_CONTENTTREE_LINK PRIMARY KEY (TREEID, ARTICLEID);


/* Foreign keys definition */

ALTER TABLE ARTICLE_CONTENTTREE_LINK ADD CONSTRAINT FK_ARTICLE_CONTENTTREE_LINK_AID FOREIGN KEY (ARTICLEID) REFERENCES ARTICLE (RELID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ARTICLE_CONTENTTREE_LINK ADD CONSTRAINT FK_ARTICLE_CONTENTTREE_LINK_TID FOREIGN KEY (TREEID) REFERENCES CONTENTTREE (RELID) ON DELETE CASCADE ON UPDATE CASCADE;


/* Indices definition */

CREATE UNIQUE INDEX IDX_ARTICLE_CONTENTTREE_LINK ON ARTICLE_CONTENTTREE_LINK (ARTICLEID, TREEID);


--------------------------------------------------------------------------

with best regards

Nils Bödeker


--

_________________________________

Verlag Eugen Ulmer
Datenbanken und IT-Entwicklung

Nils Bödeker
Bürgerwohlsweg 7
D-28215 Bremen
Germany

Tel: +49 (0)421 - 3795020
Fax: +49 (0)421 - 3795021
Mobil: +49 (0) 172 - 7468066

nilsboedeker@...
www.ulmer.de / www.nbsoft.de

yahoo ID: nilsboedeker
Skype ID: nilsboedeker
Second Life: Nils Burt
ICQ ID: 206474523