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
[email protected]
www.ulmer.de / www.nbsoft.de
yahoo ID: nilsboedeker
Skype ID: nilsboedeker
Second Life: Nils Burt
ICQ ID: 206474523
