Hi all,

I've modified the query with the specified points and thereby put the times, there is still a difference between Firebird and MySQL but the difference is getting smaller. Thanks for that.

Are there any other options .... ?


Firebird (Execution time: +/- 0.878s):

SELECT FIRST 30 SKIP 0 distinct tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem
WHERE
tr.NODELEVEL =5 and tr.LINKTYPE <> 5 and tr.ItemType = 2 AND tr.Market1 = 1 and
(lower(coalesce(art.Artikelnummer , '') || ';'
|| coalesce(art.Artikelnr_leverancier, '') || ';'
|| coalesce(art.Artikelnaam1, '') || ';'
|| coalesce(art.Artikelnaam2, '') || ';'
|| coalesce(art.MERK, '') || ';'
|| coalesce(art.EAN, '') || ';'
|| coalesce(art.ALTARTNR1, '') || ';'
|| coalesce(art.ALTARTNR2, '')

) like '%a4%')

AND (lower(coalesce(art.Artikelnummer , '') || ';'
|| coalesce(art.Artikelnr_leverancier, '') || ';'
|| coalesce(art.Artikelnaam1, '') || ';'
|| coalesce(art.Artikelnaam2, '') || ';'
|| coalesce(art.MERK, '') || ';'
|| coalesce(art.EAN, '') || ';'
|| coalesce(art.ALTARTNR1, '') || ';'
|| coalesce(art.ALTARTNR2, '')

) like '%papier%')

ORDER BY tr.NodePosition



MySQL (Execution time: +/- 0.6502s):

SELECT distinct tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem and tr.NODELEVEL =5 and tr.LINKTYPE <> 5 and tr.ItemType = 2 AND tr.Market1 = 1
WHERE

concat_ws(';', art.artikelnummer, art.artikelnaam1, art.artikelnaam2, art.ean, art.merk, art.ALTARTNR1, art.ALTARTNR2) like '%a4%' and
concat_ws(';', art.artikelnummer, art.artikelnaam1, art.artikelnaam2, art.ean, art.merk, art.ALTARTNR1, art.ALTARTNR2) like '%papier%'

ORDER BY tr.NodePosition
limit 30;

--- In firebird-support@yahoogroups.com, "Fabiano" <fabianoaspro@...> wrote:
>
> Thanks Poul!
>
>
>
> The unique problem with this approach is when you have a null value in one
> of these columns it will not work correctly.
>
> So, the 'correct' way is use coalesce do convert null value in empty string:
>
> (LCASE(coalesce(art.Artikelnummer , '')||
> coalesce(art.Artikelnr_leverancier, '')) like '%a4 papier%')
>
> Another tip is create a calculated column in the table with the above
> statement - with the name find_name.
>
> So, you can easily write a simple SQL like:
>
> Select * from some_table where find_name like '%a4 papier%'
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>