Hello,

setting statistics results in the same plan:

PLAN JOIN (IWADISOBJE_2 INDEX (IX_IWADISOBJECT_BOLD_ID), GLOBALCONT_1
INDEX (IX_GLOBALCONTEXT_BOLD_ID))
PLAN JOIN (JOIN (DOCUMENT_1 INDEX (IX_DOCUMENT_BOLD_ID), BANNEDOBJE_1
INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN (BUSINESSCL_1 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1), USER__1 INDEX (IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_3 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__2
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_4 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_2 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__3 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_5 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(CLIENT_1 INDEX (IX_CLIENT_BOLD_ID), BUSINESSCL_3 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (DOCUMENT_2 INDEX (IX_DOCUMENT_BOLD_ID), BANNEDOBJE_2
INDEX (IX_BANNEDOBJECTS_USER_BANAON)), JOIN (BUSINESSCL_4 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1), USER__4 INDEX (IX_USER__BOLD_ID)))
PLAN JOIN (IWADISOBJE_6 INDEX (IX_IWADISOBJECT_BOLD_ID), USER__5
INDEX (IX_USER__BOLD_ID))
PLAN JOIN (IWADISOBJE_7 INDEX (IX_IWADISOBJECT_BOLD_ID), JOIN
(BUSINESSCL_5 INDEX (IX_BUSINESSCLASSESROOT_BO1V1), USER__6 INDEX
(IX_USER__BOLD_ID)))
PLAN JOIN (JOIN (IWADISOBJE_8 INDEX (IX_IWADISOBJECT_BOLD_ID),
ROLEOBJECT_1 INDEX (IX_ROLEOBJECTSHOLDER_BOLD_ID)), JOIN (ROLE__1
INDEX (IX_ROLE__BOLD_ID), BUSINESSCL_6 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (JOIN (DOCUMENT_3 INDEX (IX_DOCUMENT_BOLD_ID), IWADISOBJE_9
INDEX (IX_IWADISOBJECT_ROLE_COWOBV2)), JOIN (ROLE__2 INDEX
(IX_ROLE__BOLD_ID), BUSINESSCL_7 INDEX
(IX_BUSINESSCLASSESROOT_BO1V1)))
PLAN JOIN (IWADISOBJE_1 INDEX (IDXIWADISOBJECT1), WORD_1 INDEX
(IDXWORD3))

Prepare time: 4075 ms
Execution time: 691664 ms
Fetch time: 3775 ms

Then I did a
drop index IdxIwadisobject1
create index IdxIwadisobject1 on iwadisobject (bold_type)
but the query still runs for 665000 ms.

Then I did a
drop index IdxWord1
create index IdxWord1 on Word (data)
but the query still runs for 685000 ms.

Dropping the combined indexes and use one single-column index results
in no better execution time.

It seems that Firebird 2.1 and 2.0.3 have massive problems with
optimizing my query when table word contains as in my case 15.000.000
records.

What could be done decrease the runtime of the like %-query to the
time the =-query needs?

Thanks

Stefan


--- In firebird-support@yahoogroups.com, "Anderson Farias"
<peixedragao@...> wrote:

>
> Hi,
>
> Those plans are diferent, and that's where the problem is.
>
> using = ::
>
> PLAN JOIN (
> WORD_1 INDEX (IDXWORD1),
> IWADISOBJE_1 INDEX (IX_IWADISOBJECT_BOLD_ID))
>
> using like/starting ::
>
> PLAN JOIN (
> IWADISOBJE_1 INDEX (IDXIWADISOBJECT1),
> WORD_1 INDEX (IDXWORD3))
>
> BUT, testing the database you sent (on FB 2.1 final) both querys

have the

> same PLAN (the best one) but of course since there's no data. I'd

try

> updating your index statistics and see what happens.
>
> If the problem persists, try droping index IDXIWADISOBJECT1. If you

really

> need it, try creating it only for column BOLD_TYPE. With FB

*usually* n

> single field indexes work better than compound indexes. so you

could only

> have
>
> someIDX1 on BOLD_TYPE and someIDX2 on BOLD_ID and *no* index on

both

> (BOLD_TYPE, BOLD_ID)
>
> (the same may apply to IDXWORD1 and IDXWORD3)
>
>
> HTH,
>
> Regards
> Anderson Farias
>