I have a table called PRODUCTS. It has a field of language. I want to list all rows of
language = 'es'
products AS source
LEFT JOIN products AS target ON source.id_products = target.id_products
AND source.`language` = 'es'
AND target.`language` = 'en'
target.id_products IS NULL
My guess is that this is happening due to lack of indexes on the table.
Try adding index on (
language) , that should speed up your query.
In addition you can try to use NOT EXISTS() instead of a left join, maybe it will speed things up a bit as well:
SELECT * FROM products t WHERE t.language = 'es' AND NOT EXISTS(SELECT 1 FROM products s WHERE s.language = 'en' and s.id_products = t.id_products)