Hi,

this is not true that FB use 10 full table scans
it use only one table scan and check 10 combination of fields

try this on emplooye.fdb database
SELECT *
FROM CUSTOMER r
where r.CUSTOMER like '%S%'
15 seq reads
and then try

SELECT *
FROM CUSTOMER r
where r.CUSTOMER like '%S%' or r.CUSTOMER like '%B%' or r.CUSTOMER like '%M%'

you got the same 15 seq reads

if you you notice more reads, then this is an error and you should report it into fbtracker

Karol Bieniaszewski

--- In firebird-support@yahoogroups.com, "Fabiano" <fabianoaspro@...> wrote:
>
> I think the problem is the use of "LCASE(something) like '% something%'"
>
> When FB encounter a %% statement it will perform a full table scan. If you
> have 10 statements like this (and you have much more) you have at least 10
> full table scans to do the job.
>
>
>
> First: Rewrite your SQL code and DO NOT USE no one %% statement. Check if
> you have the correct indexes (tr.ItemType, tr.Marke1, tr.NODELEVEL,
> tr.LINKTYPE) - Try one index for all these fields first.
>
>
>
> When you finish you will have a very fast statement. The next step is create
> concatenate all columns in a virtual one and do only one %% statement.
> Example:
>
>
>
> Istead of:
>
> (LCASE(art.Artikelnummer) like '%a4 papier%') or
> (LCASE(art.Artikelnr_leverancier) like '%a4 papier%')
>
>
>
> Use:
>
> (LCASE(art.Artikelnummer|| art.Artikelnr_leverancier) like '%a4 papier%')
>
>
>
> In this case FB will do only ONE full table scan instead of 2 from the above
> example.
>
>
>
> Sorry about my poor English, I hope you can understand my point of view. If
> you need more help create a simple database with these tables populated
> inside and publish on the internet and answer with the link to download.
>
>
>
> Fabiano.
>
>
>
> [Non-text portions of this message have been removed]
>