8
I have this SQL here:
SELECT id, nome, url FROM categorias WHERE status = 1 AND id_ls IN
(SELECT id_categoria FROM cliente_categorias) GROUP BY url
What he does is seek only categories that have customers assigned to them.
My table categories has 1,477 records and cliente_categories 23,616:
It works. The problem is that the load is very slow. The query takes about 17 seconds. There is some way I can improve?
How many records are there? Do cliente_categories need a distinct ? Place the diagram of these two tables!
– novic
You are using index or cache ?
– Valdeir Psr
I don’t understand why
GROUP BY
.– Victor Stafusa
@Virgilionovic Hi Virgilio! Thanks for contributing, I edited the question with more :)
– Aryana Valcanaia
Try using EXISTS(select top 1 1 from ... ) instead of IN
– Zorkind
@Victorstafusa needs to group some results because of an external issue to the project. this database pulls data from an offline database that has some structure problems. But removing the group by in no way changes the load.
– Aryana Valcanaia
If that
GROUP BY
do not change the outcome at all, I recommend taking. I see it as something that can inhibit optimizations that the database would do if you try something that is in the answers below.– Victor Stafusa
How long was my query? -P
– Zorkind
What is the difference between the columns
id
andid_ls
on the tablecategorias
? On the tablecategorias
the primary key is the columnid
, but in the code that posted Join is established with the columnid_ls
. I missed that.– José Diz
@Aryanavalcanaia What is the database manager? (mariaDB, SQL Server, Oracle Database etc)
– José Diz
A customer can be in the same category multiple times?
– Victor Stafusa