--- In firebird-support@yahoogroups.com, "Kevin Herrmann" 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.

Well, maybe you've included all things you need, but not everything I
need - you forgot the plan when you remove ORDER BY.

> 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;

Hmm, strange query. Why select a.EVENTTYPE when it will always be
null? Later on you demand that a.ID1 is null, something which means
(in my eyes, you better check) that your query can be rewritten as:

select d.*, e.NAME, e.GIVENNAME from documents d
join entity e on d.subject_ID=e.id
where d.doc_type in (5,6,7)
and e.status=0
and e.name <> 'MACRO'
and e.AttendingID=2
and not exists(select * from auditmessage a
where d.id = a.id1 and a.eventtype = 19)
order by d.doc_category;

As you can see, I also removed 'left' from entity, I don't think there
should be any difference in the result set between a left [outer] join
and an [inner] join in this case (again, I may be wrong).

> There is an index on doc_category, but the optimizer doesn't want to
> use it. Here is the Plan:

Well, why should it? The return set isn't limited to documents, and
the result set has already been limited from that table due to the
doc_type being between 5 and 7. But you do limit the choices of the
optimizer by using left join rather than inner joins.

HTH,
Set

> 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)))
>
> 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);