Hello Zlatko,

creating an index on word (data) doesn't speed the query up. Any
other ideas?

Stefan

--- In firebird-support@yahoogroups.com, "zlatko.ivankovic"
<zlatko.ivankovic@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "swestner" <s@...> wrote:
> >
> > Hello,
> >
> > we use Delphi and Bold For Delphi to access a Firebird-DB. Bold

is

> an
> > OR-mapper whichs converts our OCL into SQL.
> >
> > In our program we do a complex search which produces the

following

> > SQL:
> >
> > SELECT IwadisObje_1.BOLD_ID, IwadisObje_1.BOLD_TYPE
> > FROM IwadisObject IwadisObje_1 JOIN Word Word_1 ON
> > (IwadisObje_1.BOLD_ID = Word_1.indexedObject)
> > WHERE (Word_1.data LIKE 'SCHAAF%')
>
> > CREATE TABLE Word ( BOLD_ID INTEGER NOT NULL, BOLD_TYPE
> SMALLINT
> > NOT NULL, attribute VARCHAR(50) DEFAULT '' COLLATE DE_DE ,
> data
> > VARCHAR(40) DEFAULT '' COLLATE DE_DE , phoneticData VARCHAR

(40)

> > DEFAULT '' COLLATE DE_DE , indexedObject INTEGER DEFAULT -

1 ,

> > CONSTRAINT IX_Word_BOLD_ID PRIMARY KEY (BOLD_ID))
> > CREATE INDEX IX_Word_BOLD_TYPE ON Word (BOLD_TYPE)
> > CREATE INDEX IX_Word_indexedObject ON Word (indexedObject)
> >
> > We created additional indexes already (which are used in other
> > queries):
> >
> > create index IdxUser1 on User_ (login, password_)');
> > create index IdxIwadisObjectFBB on iwadisobject (folder, BOLD_ID,
> > BOLD_TYPE)');
> > create index IdxWord1 on Word (attribute, data)');
> > create index IdxWord2 on Word (attribute, phoneticdata)');
> > create index IdxWord3 on Word (indexedObject)');
>
> > The amount of record per table is:
> > iwadisobject 90744
> > word 5806405
> > globalcontext 1
> > user_ 34
> > client 20
> > role_ 107
> >
> > Does anybody has some ideas?
> >
>
> Try this:
>
> create index IdxWord4 on Word (data)
>
>
>
> Regards,
> Zlatko
>