Bring all necessary results in a query - Mariadb

Question

I need to reformulate a small system, which today, works but opening multiple connections to the database, and when there are too many records, the database falls, or when that doesn’t happen, the page gets very slow.

So, to get around this problem, I’m working on a query that brings me all the results at once, and then I treat this information on the PHP side to improve performance.

I’m only having a small problem: the table, which I will call a test, can have multiple relations with test2 or test3, so I created an item_test table to relate them, but the information in table test2 repeats when I run the query.

Follow an example on SQL Fiddle

This is the query I’m using:

select distinct t1.teste, 
GROUP_CONCAT(t2.teste2 SEPARATOR '&') teste2 ,
GROUP_CONCAT(t3.teste3 SEPARATOR '&') teste3 
FROM item_teste it
join teste t1 on t1.id_teste = it.id_teste
join teste2 t2 on t2.id_teste2 = it.id_teste2
join teste3 t3 on t3.id_teste3 = it.id_teste3
where t1.teste like '%testando%'
group by t1.id_teste;

I don’t know if this is the best way to optimize, so there is some other way to optimize queries or I’m on the right track?

Answer 1

Not to repeat the test information 2 (I did not test but I believe it will solve) add distinct ex: group_concat(distinct t2.teste2 SEPARATOR '&')