--- In firebird-support@yahoogroups.com, "markd_mms" <spam@...> wrote:

>
> I'm having trouble with the following query. When I run the

following

> it returns 14016 record in just over 4 seconds:
>
> SELECT ITEM.TITLE AS "Title", ITEM.TITLE2 AS "Title2",
> ITEMINFORMAT.CATALOGUENO AS "Catalogue Number",
> ITEMINFORMAT.BARCODE AS "Barcode", FORMATS.DESCRIPTION AS "Format",
> DISTRIBUTORS.DESCRIPTION AS "Distributor", GENRES.DESCRIPTION

AS "Genre",

> ITEM.DISTRIBUTOR_RELEASE_DATE AS "Release Date", ITEM.ITEMID
> FROM ITEM
> LEFT JOIN ITEMINFORMAT ON ITEMINFORMAT.ITEMID = ITEM.ITEMID
> LEFT JOIN ACTORITEM ON ITEM.ITEMID = ACTORITEM.ITEMID
> LEFT JOIN ACTORS ON ACTORITEM.ACTORID = ACTORS.ACTORID
> LEFT JOIN FORMATS ON ITEMINFORMAT.FORMATID = FORMATS.FORMATID
> LEFT JOIN DISTRIBUTORS ON ITEM.DISTRIBUTORID =

DISTRIBUTORS.DISTRIBUTORID

> LEFT JOIN GENRES ON ITEM.GENREID = GENRES.GENREID
>
> but as soon as I add
>
> ORDER BY ITEM.TITLE ASC
>
> the query takes over 4 minutes to run. There is an ascending index

on

> ITEM.TITLE that I tried removing but it doesn't make a difference.
>
> Does anyone know why it could be making such a big difference?
>
> TIA
> Mark
>

It could take much more time to read the index, read the data, read
the index, read the data than to read it in storage order and sort in
memory.

You could try changing the order by to

ORDER BY ITEM.TITLE || '' ASC

This would force the index to not be used.

Otherwise, post the plan of the fast query and slow query so we can
see the likely culprits.

Adam