Query with CONCAT inside JOIN is slow

Question

I need to get different values from 3 different tables (table1, table2, table3) where the common value is a reference number. This number appears on all 3 tables, except on table3 where the number is divided on three different columns. I tried to make a LEFT OUTER JOIN concatenating these three columns to make the whole reference number, but the query becomes significantly slower. This is the part of the query where the issue is found:

SELECT t1.type AS type, t2.client AS client, t3.somenumber AS somenumber, t4.anothernumber AS anothernumber

FROM table1 t1 JOIN table2 t2 ON t1.somevalue = t2.somevalue
JOIN table4 t4 ON t4.reference_number = t1.reference_number --Some validation I need to make on another table

--Here's the problem. table3's values 1 through 3 make the reference number found in the other tables.
--The CONCAT makes the query significantly slow.
LEFT OUTER JOIN table3 t3 ON CONCAT(t3.value1, t3.value2, t3.value3) = t1.reference_number

WHERE t1.date BETWEEN '2022-04-01' AND '2022-05-01'
AND t1.client IN ('client1', 'client2', 'client3', 'client4', 'client5') 

GROUP BY t1.reference_number --Group by the reference number

I tried making a view to create a column where the reference number is 'stored', but it still takes a lot to run the query. Is there a way to optimize this?

Running on 10.3.32-MariaDB

Answer 1

The GROUP BY does not need CONCAT:

GROUP BY t3.value1, t3.value2, t3.value3

I don't understand why you tacked on t1.reference_number; it is either similar info or NULL. The NULL case might lead to extra groups, by it seems like a waste. (Add it on if necessary.)

Indexes:

t1:  INDEX(date)
t1:  INDEX(client, date)
t2:  INDEX(somevalue,  client)
t3:  INDEX(value1, value2, value3)
t4:  INDEX(reference_number)

Was t3.value a typo for t3.value3?

Consider getting rid of t4; you are not using any values from it. The only thing it is doing is to verify that table4 has a matching row.

What version of MySQL are you using?

It may be useful to have VIRTUAL or PERSISTENT (generated) column that is CONCAT (value1, value2, value3) and index it.

(And I agree with the Commenters that the "reference number" is ambiguous.)