Hi Kevin!

>I've got a query that I threw together to serve as a search in a web app.
>My first cut of it used "containing" operators for the user's search terms and was
>taking 15-20 seconds to run, which didn't surprise me. I figured I would have to do
>something smarter than "containing". As I began to play around with optimization, I
>came to find that the "containing" operators weren't my problem. My problem was my
>"distinct" and my joins.
>
>I prowled through the mailing list last Friday looking for leads, tried a lot of
>things, but never could get just the joins, without search criteria, running in much
>under about 14 seconds.
>
>select distinct s.StudentSeq, s.LastName, s.FirstName,
> s.BirthDate, s.MiddleName, p.StudentID
>from student s
>join PrfStuID p on s.StudentSeq=p.StudentSeq
>join AliasStu a on s.StudentSeq=a.StudentSeq
>join SchlHist h on s.StudentSeq=h.StudentSeq
>join Facility f on h.FacilityID=f.FacilityID
>join District d on f.DistrictCode=d.DistrictCode
>
>This database is old and hoary. I'll include the DDL, at least for the important
>fields and indexes. There are no primary keys on any tables, although every table has
>a single unique index. The plan looks like this:
>
>PLAN SORT (JOIN (F NATURAL, D INDEX (DISTRICT_), H INDEX (SCHLHIST_FACILITYID),
>S INDEX (STUDENT_), P INDEX (PRFSTUID_), A INDEX (ALIASSTU_STUDENTSEQINDEX)))

The first impression is that this is a fair query with a decent plan, or at least as decent as it gets without any WHERE clause. However, many web apps desire to show only a fraction of the records on screen, and that can be done without distinct (so that you do not have to select all records):

select s.StudentSeq, s.LastName, s.FirstName, s.BirthDate, s.MiddleName, p.StudentID
from student s
join PrfStuID p on s.StudentSeq=p.StudentSeq
where exists(select * from AliasStu a
join SchlHist h on a.StudentSeq=h.StudentSeq
join Facility f on h.FacilityID=f.FacilityID
join District d on f.DistrictCode=d.DistrictCode
where s.StudentSeq=a.StudentSeq)
ROWS 1 TO 20

(you may want to add ORDER BY and possibly use FIRST rather than ROWS if you use an old Firebird version, of course 1 TO 20 was randomly chosen, use whatever fits your need).

However, if you want all rows returned, then I don't think my suggestion will help you anything.

HTH,
Set