It is using the doc_type index to speed up the "where" clause. You
can instruct FB on which plan to use by including it before
the "order by" if you think that is a good idea.

Adam


--- In firebird-support@yahoogroups.com, "Kevin Herrmann"
<kevinherrmann@y...> wrote:

>
> This query is horribly slow on Firebird 1.0.3 UNTIL I remove the
> 'order by'. I think I've included all things I need. Here is the
> relevant Info:
>
> select d.*, e.NAME, e.GIVENNAME, a.EVENTTYPE from documents d
> left join entity e on d.subject_ID=e.id
> left join auditmessage a on d.id = a.id1 and a.eventtype = 19
> where d.doc_type in (5,6,7)
> and e.status=0
> and e.name <> 'MACRO'
> and e.AttendingID=2
> and a.ID1 is null
> order by d.doc_category;
>
> There is an index on doc_category, but the optimizer doesn't want to
> use it. Here is the Plan:
>
> PLAN SORT (JOIN (JOIN (D INDEX
> (DOCUMENTS_TYP_X,DOCUMENTS_TYP_X,DOCUMENTS_TYP_X),E INDEX
> (RDB$PRIMARY8)),A INDEX

(AUDITMESSAGE_EVENTTYPE_X,AUDITMESSAGE_ID1_X)))

>
> Adapted Plan
> PLAN SORT (JOIN (JOIN (D INDEX
> (DOCUMENTS_TYP_X,DOCUMENTS_TYP_X,DOCUMENTS_TYP_X),E INDEX
> (INTEG_36)),A INDEX (AUDITMESSAGE_EVENTTYPE_X,AUDITMESSAGE_ID1_X)))
>
>
> Here is the DDL:
>
> CREATE TABLE DOCUMENTS (
> ID INTEGER NOT NULL,
> DOC_TYPE SMALLINT NOT NULL,
> SUBJECT_ID INTEGER,
> SESSION_ID INTEGER,
> CREATED DATE NOT NULL,
> METADOCUMENT_ID INTEGER,
> ATTRIBUTES INTEGER,
> DESCRIPTION VARCHAR(250),
> DOC_CATEGORY SMALLINT
> );
> ALTER TABLE DOCUMENTS ADD PRIMARY KEY (ID);
> CREATE INDEX DOCUMENTS_CAT_X ON DOCUMENTS (DOC_CATEGORY);
> CREATE INDEX DOCUMENTS_SID_X ON DOCUMENTS (SUBJECT_ID);
> CREATE INDEX DOCUMENTS_TYP_X ON DOCUMENTS (DOC_TYPE);
>
> CREATE TABLE ENTITY (
> ID ENIGMA_ID /* INTEGER NOT NULL */ NOT

NULL,

> ENTITYTYPE SMALLINT NOT NULL,
> NAME VARCHAR(48) NOT NULL,
> ROLE_IDS VARCHAR(64),
> BIRTH DATE,
> MRNUM VARCHAR(16),
> SS CHAR(9),
> SEX CHAR(1),
> RACE VARCHAR(16),
> STATUS INTEGER,
> PAGER VARCHAR(20),
> EMAIL VARCHAR(48),
> TITLE VARCHAR(48),
> DEPARTMENT VARCHAR(48),
> COMPANY VARCHAR(48),
> ISPATIENT CHAR(1),
> ISEMPLOYEE CHAR(1),
> ISPHYSICIAN CHAR(1),
> PHOTO BLOB SUB_TYPE -3 SEGMENT SIZE 1024,
> HOME_ID ENIGMA_ID /* INTEGER NOT NULL */,
> WORK_ID ENIGMA_ID /* INTEGER NOT NULL */,
> DEA VARCHAR(20),
> AMA VARCHAR(20),
> PREFIX SMALLINT,
> SUFFIX CHAR(16),
> IVERSION SMALLINT NOT NULL,
> IOBSOLETE CHAR(1),
> CLASSSIGNATURE VARCHAR(64),
> PHYSICIANS VARCHAR(128),
> USEROID INTEGER,
> ATTENDINGID INTEGER,
> MIDDLE VARCHAR(16),
> OTHERMRNUMS VARCHAR(40),
> NOTEBOOK VARCHAR(2048),
> PHARMACY_NAME VARCHAR(64),
> PHARMACY_PHONE CHAR(20),
> PHARMACY_FAX CHAR(20),
> USER_SETTINGS BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> ISGUARANTOR CHAR(1),
> GUARANTORID INTEGER,
> GUARANTORRELATIONSHIP CHAR(32),
> INSURANCEID_1 INTEGER,
> INSURANCEID_2 INTEGER,
> INSURANCEID_3 INTEGER,
> SIGNATURE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> GIVENNAME VARCHAR(64)
> );
> ALTER TABLE ENTITY ADD PRIMARY KEY (ID);
> CREATE INDEX ENTITY_ATTENDING ON ENTITY (ATTENDINGID);
> CREATE INDEX ENTITY_GIVENNAME ON ENTITY (GIVENNAME);
> CREATE INDEX ENTITY_NAME ON ENTITY (NAME);
> CREATE INDEX ENTITY_ROLE ON ENTITY (ISPATIENT, ISEMPLOYEE,

ISPHYSICIAN);

> CREATE INDEX ENTITY_STATUS ON ENTITY (STATUS);
> CREATE INDEX ENTITY_USEROID ON ENTITY (USEROID);
>
> CREATE TABLE AUDITMESSAGE (
> EVENTTYPE SMALLINT,
> EVENTDATE DATE,
> USERNAME CHAR(16),
> ID1 INTEGER,
> ID2 INTEGER,
> AUDITTEXT VARCHAR(255)
> );
> CREATE INDEX AUDITMESSAGE_EVENTDATE2_X ON AUDITMESSAGE (EVENTDATE);
> CREATE DESCENDING INDEX AUDITMESSAGE_EVENTDATE_X ON AUDITMESSAGE
> (EVENTDATE);
> CREATE INDEX AUDITMESSAGE_EVENTTYPE_X ON AUDITMESSAGE (EVENTTYPE);
> CREATE INDEX AUDITMESSAGE_ID1_X ON AUDITMESSAGE (ID1);
> CREATE INDEX AUDITMESSAGE_USERNAME_X ON AUDITMESSAGE (USERNAME);