Cesar Cesar - 7 months ago 19
SQL Question

Speed of LEFT JOIN in the same table - Mysql

I have a table called PRODUCTS. It has a field of language. I want to list all rows of

language = 'es'
which do lack a traduction (corresponding ID) in other language. I have tried the following (id_products is the key relating rows of the same product in different language). It is extremely slow (seconds for a few thousand rows):

SELECT
*
FROM
products AS source
LEFT JOIN products AS target ON source.id_products = target.id_products
AND source.`language` = 'es'
AND target.`language` = 'en'
WHERE
target.id_products IS NULL

Answer

My guess is that this is happening due to lack of indexes on the table.

Try adding index on (id_products,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)