--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
[snip]
> You could try changing the order by to
>
> ORDER BY ITEM.TITLE || '' ASC
>
> This would force the index to not be used.
That still took a few minutes.
> Otherwise, post the plan of the fast query and slow query so we can
> see the likely culprits.
This is the plan for the few second query:
Plan:
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM NATURAL,ITEMINFORMAT
INDEX (PK_ITEMINFORMAT)),ACTORITEM NATURAL),ACTORS INDEX
(PK_ACTORS)),FORMATS INDEX (PK_FORMATS)),DISTRIBUTORS INDEX
(PK_DISTRIBUTORS)),GENRES INDEX (PK_GENRES))
Adapted plan:
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM NATURAL,ITEMINFORMAT
INDEX (PK_ITEMINFORMAT)),ACTORITEM NATURAL),ACTORS INDEX
(PK_ACTORS)),FORMATS INDEX (PK_FORMATS)),DISTRIBUTORS INDEX
(PK_DISTRIBUTORS)),GENRES INDEX (PK_GENRES))
and this is the plan for the few minute query:
Plan:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM
NATURAL,ITEMINFORMAT INDEX (PK_ITEMINFORMAT)),ACTORITEM
NATURAL),ACTORS INDEX (PK_ACTORS)),FORMATS INDEX
(PK_FORMATS)),DISTRIBUTORS INDEX (PK_DISTRIBUTORS)),GENRES INDEX
(PK_GENRES)))
Adapted plan:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM
NATURAL,ITEMINFORMAT INDEX (PK_ITEMINFORMAT)),ACTORITEM
NATURAL),ACTORS INDEX (PK_ACTORS)),FORMATS INDEX
(PK_FORMATS)),DISTRIBUTORS INDEX (PK_DISTRIBUTORS)),GENRES INDEX
(PK_GENRES)))