0
I wonder if the one way I can optimize this sql code (mysql) that I did, the search is too slow, taking too long to bring the selected fields, I’ve done some tests but it’s still the same.
SELECT
rc470.cod_item as 'Código do Produto',
r0200.descr_item as 'Descrição',
GROUP_CONCAT(DISTINCT rc470.cst_icms) as 'CSTs'
FROM rc470
INNER JOIN r0200
ON rc470.cod_item = r0200.cod_item
GROUP BY rc470.cod_item
HAVING COUNT(DISTINCT cst_icms) >= 2;
This code makes a search in two tables, grouping the results of rc470.cod_item and rc470.cst_icms, where it will only display cod_item with more than one different cts_icms and Inner Join to look for the product description in R0200 table that has the same cod_item with rc470 table.
I hope I was clear
Thank you
Removes the
DISTINCTof Group_concat, this affects performance and is unnecessary– Maicon Carraro
I suspect the slowness is caused by
group_concatanddistinct, can you justify the use of them? also puts the structure of the tables, maybe it is possible to write the query otherwise.– rray
If you just do
SELECT * FROM FROM rc470
INNER JOIN r0200
ON rc470.cod_item = r0200.cod_item, how many lines go back and how long the query takes? You have a foreign key constraint in that columncod_item?cod_itemis the primary key of any of the tables?– user25930
I use GROUP_CONCAT (DISTINCT column) to show only different values and group the same, I will try to better exemplify with images tomorrow that I will have access to the bank, but thanks for the personal tip.
– Matheus de Moraes