At 05:45 PM 07-06-02 +0100, you wrote:

>Seeing we are on a roll and you all like a challenge :-) I have another slow
>query for you.
>
>This query takes between 11 and 19 seconds.
>
>Book table has 33043 records
>Author table has 16325 records
>Location table has 6 records
>Deweyno table has 6226 records
>Publisher table has 4685 records
>
>SELECT
> B.BOOK_ID,
> B.REFERENCE_NUMBER,
> B.IS_SHORT_LOAN,
> B.TITLE,
> B.DATE_DUE_BACK,
> B.IS_DELETED,
> B.RESERVED_BORROWER_ID,
> B.DATE_RESERVED,
> B.DEWEY_NO_ID,
> A.NAME,
> L.DESCRIPTION,
> D.DEWEY_NO
>FROM
> ((((BOOK B LEFT OUTER JOIN LOCATION L on B.LOCATION_ID = L.LOCATION_ID)
> LEFT OUTER JOIN AUTHOR A on B.AUTHOR_ID = A.AUTHOR_ID)
> LEFT OUTER JOIN DEWEYNO D on B.DEWEY_NO_ID = D.DEWEY_NO_ID)
> LEFT OUTER JOIN PUBLISHER P on B.PUBLISHER_ID = P.PUBLISHER_ID)
>ORDER BY
> B.TITLE,
> B.REFERENCE_NUMBER
>
>The plan is:
>PLAN SORT (JOIN (JOIN (JOIN (JOIN (B NATURAL,L INDEX (LOCATION0)),A INDEX
>(AUTHOR0)),D INDEX (DEWEYNO0)),P INDEX (PUBLISHER0)))
>
>The outer joins are needed so that, for example, if a book has no author
>(and therefore no author_id) it is still listed. This query should list all
>books.

It's a golden rule for performance that you need to index columns on which
you are going to perform sorts, where searches or joins. You are getting a
natural sort on Title because you apparently have no index for it. You are
getting a performance hit on Location because you are using a
low-selectivity index on it.

As a tip, don't index Book.Title, but add a new column of the same
specification and add a BEFORE INSERT trigger to copy the uppercase
conversion of Title into this column. Place an index on this "Title_upper"
column and use it for your ordering. You'll also find this column will be
invaluable for searching on titles, something a library app always needs to do.

For the Location column, remove the index on Book.Location_ID and replace
it with a composite index on (Location_ID, Book_ID), in that order. (Or,
you could instead make the index from (Dewey_No_ID, Location_ID) for the
purpose of this query).

I can't see why Publisher is joined into this query. If you don't need any
columns from Publisher, removing that extra join would help too.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________