Your Location is: Home > Mysql

Getting the field of MySQL rows that don't match my needs results in an execution that runs almost forever

From: Romania View: 1640 JarsOfJam-Scheduler 
 mysql  join  left-join  entity-attribute-value

Question

I have three tables:

  • feature_value

  • feature_product

  • product

My goal is to get all the feature_value that don't belong to any product. I had an idea consisting in using LEFT JOIN so that I get all the possible combinations and then, filter out the rows that don't have a value for a field of the following tables: feature_product and product (in other words: i'd want to get the items that are not associated to any product or that are associated to at least one product BUT some of these products don't exist).

So I've created the following request, but it runs forever and I don't understand why:

SELECT table_feature_value.id_feature_value FROM feature_value as table_feature_value

LEFT JOIN feature_product as table_feature_product ON table_feature_product.id_feature = table_feature_value.id_feature

LEFT JOIN product as table_product ON table_product.id_product = table_feature_product.id_product


WHERE ( table_feature_product.id_feature IS NULL OR table_product.id_product IS NULL )

Could you please tell me why it doesn't work?

Best answer

Move the criteria in the WHERE clause to the ON clause of the appropriate table join:

SELECT fv.id_feature_value
FROM feature_value fv
LEFT JOIN feature_product fp
    ON fp.id_feature = fv.id_feature AND fp.id_feature IS NULL
LEFT JOIN product p
    ON p.id_product = fp.id_product AND p.id_product IS NULL
WHERE
    fv.custom = 1;

This issue here is that restrictions in the WHERE clause on any table other than the first one invovled in the join may prematurely filter off records from the result set.

Another answer

I would use not exists:

select fv.*
from feature_value fv
where not exists (
    select 1 
    from product p
    inner join feature_product fp on fp.id_product = p.id_product
    where fp.id_feature = fv.id_feature
)

Tags