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 it does is fetch only categories that have clients assigned to them.
My categories table has 1,477 records and customer_categories 23,616:
It works. The problem is that the load is soooo slow. The query takes about 17 seconds. Is there any way for me to improve?
Try this:
SELECT c.id, c.nome, c.url
FROM categorias c
INNER JOIN cliente_categorias d ON d.id_categoria = c.id_ls
WHERE c.status = 1
GROUP BY c.url
Also try to create an index to make this type of query faster:
ALTER TABLE cliente_categorias ADD INDEX idx_categoria_categorias_cliente (id_categoria);
Make the test use distinct , example:
SELECT id, nome, url FROM categorias WHERE status = 1 AND id_ls IN
(SELECT distinct id_categoria FROM cliente_categorias) GROUP BY url
Try with EXISTS
SELECT id, nome, url FROM categorias WHERE status = 1
AND EXISTS(select top 1 1 FROM cliente_categorias where id_categoria = id_ls) GROUP BY url
I was able to solve with everyone's help. In fact, creating indexes has improved loading for 4 seconds (still a bit slow) but I believe this is due to the number of records.
Some remarks according to what everybody posted there:
The query remained the same, I just added the indexes in the tables involved.