Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread SPEED OF JOIN IN SQL
Thu, Feb 24 2022 10:40 AMPermanent Link

Accowin

LBRP

Avatar

Hello,
I notice a big difference in speed when I use these 2 statements in the join.
Both tables are indexed on the 4 fields used in the join

SELECT h.DagbSubType, IF(h.DagbSubType=2, 'KN','FA') as fk ,b.Land, h.Intracode, b.Procent,
              SUM(-h.BedragEur) As bedragEUR, Round(SUM(l.BTwBedrag/H.koers),2) AS L_BtwBedrag,
              h.BtwRooster,   l.BtwKode, ROUND(SUM(-h.BtwBedrag/h.koers),2) AS H_BtwBedrag
FROM history h
LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line
--LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line)
LEFT JOIN BtwCodes b on l.BtwKode = b.Referte
WHERE   h.Intracode > ''
Group by h.DagbSubType, b.Land, h.Intracode, b.Procent, h.BtwRooster,  l.BtwKode

This query is very slow:
LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line

This is fast
LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line)

Why is the second JOIN faster?
Should I always use the () if possible?
Fri, Feb 25 2022 5:09 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Accowin

<<
This query is very slow:
LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line

This is fast
LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line)

Why is the second JOIN faster?
Should I always use the () if possible?
>>

In the first JOIN the engine will try to use four indexes separated for each condition and the second one will use just one compound index  for table h and one for table I. When you use () you are telling to the engine to use (if exists) an index with fields in the condition and therefore it will speed up the things a lot.

Eduardo
Mon, Feb 28 2022 9:20 AMPermanent Link

Accowin

LBRP

Avatar

<<
This query is very slow:
LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line

This is fast
LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line)
>>

In the first JOIN the engine will try to use four indexes separated for each condition and the second one will use just one compound index  for table h and one for table I. When you use () you are telling to the engine to use (if exists) an index with fields in the condition and therefore it will speed up the things a lot.


Thanks Eduardo,
that is clear.
So if there is an index on the corresponding fields, I shoud use as much as possible the compound index.
If in The example above, the field "line" should not be indexed, than I would write:
LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar) = (l.Dagboek, l.Docnr, l.jaar) and h.Lijn=l.line
Tue, Mar 1 2022 5:20 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Accowin wrote:

<<
So if there is an index on the corresponding fields, I shoud use as much as possible the compound index.
If in The example above, the field "line" should not be indexed, than I would write:
LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar) = (l.Dagboek, l.Docnr, l.jaar) and h.Lijn=l.line
>>

Yes, you have got the idea. To speed up the query execution time, the engine needs indexes for each condition and be sure that using the idea behing "row value" it is faster than using individual indexes even for each field involved in the conditions.

Eduardo
Tue, Mar 1 2022 9:20 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Accowin


Whilst I can agree with Jose I had a different interpretation of what you wrote so I'd like to ask a question to clarify things for me. You wrote "Both tables are indexed on the 4 fields used in the join" which I interpreted as there being four separate indices. Jose seems to have interprets it as there being a single compound index. Which is correct?

Roy Lambert
Wed, Jul 13 2022 10:19 AMPermanent Link

Accowin

LBRP

Avatar

Roy,

We have 1 Index on 4 fields.
Image