Hello Fabiano,

I've tried the first index, only the second index indicates which you give twice IdItem on. You mean maybe another field?

But with the first index:

SELECT FIRST 30 SKIP 0 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%')

group by tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem


Prepare time: 0.004s
Field #01: TREE3.NODEPOSITION Alias:NODEPOSITION Type:INTEGER
Field #02: TREE3.IDLINK Alias:IDLINK Type:INTEGER
Field #03: TREE3.IDTARGETLINK Alias:IDTARGETLINK Type:INTEGER
Field #04: TREE3.IDITEM Alias:IDITEM Type:INTEGER
PLAN SORT (JOIN (TR INDEX (IDX_TREE31), ART INDEX (RDB$PRIMARY2)))


592894 fetches, 0 marks, 1846 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 240385 index, 0 seq.
Delta memory: 48 bytes.
Total execution time: 1.112s

thanks,

An VanderLinde

--- In firebird-support@yahoogroups.com, "Fabiano" <fabianoaspro@...> wrote:
>
> Try this:
>
>
>
> SELECT FIRST 30 SKIP 0 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%')
>
> group by tr.NodePosition, tr.IdLink, tr.IdTargetLink, tr.IdItem
>
>
>
> Finally create na índex on Tree3 table with the fields: NodePosition,
> IdLink, IdTargetLink, IdItem
>
>
>
> I think you do not need a forced "order by" clause because it is implicitly
> formed as a group by.
>
>
>
> Measure time, then drop this index and create another on fields: iditem,
> NodePosition, IdLink, IdTargetLink, IdItem
>
>
>
> One of these Will perform a better performance.
>
>
>
> [Non-text portions of this message have been removed]
>