Mysql one-to-many query with negation and / or multiple criteria

Question

I thought that such a query would be quite easy due to the nature of relational databases, but it seems to be useful to me. I also searched around but found nothing that really helped. Here's the situation:

Say I have a simple link for products and product tags. This is a one-to-many relationship, so we can have the following:

productid | tag ======================== 1 | Car 1 | Black 1 | Ford 2 | Car 2 | Red 2 | Ford 3 | Car 3 | Black 3 | Lexus 4 | Motorcycle 4 | Black 5 | Skateboard 5 | Black 6 | Skateboard 6 | Green 

What is the most efficient way to request for everyone (Ford OR Black OR Skateboard) AND NOT (Motorcycles OR Green) ? Another question I need to do is something like (Car) or (Skateboard) or (Green AND Motorcycle) or (Red AND Motorcycle) .

The product table contains about 150 thousand records in the product table and 600 thousand records in the tag tables, so the query should be as efficient as possible. Here is one query I was messing around with (example # 1), but it seems to take about 4 seconds or so. Any help would be greatly appreciated.

 SELECT p.productid FROM products p JOIN producttags tag1 USING (productid) WHERE p.active = 1 AND tag1.tag IN ( 'Ford', 'Black', 'Skatebaord' ) AND p.productid NOT IN (SELECT productid FROM producttags WHERE tag IN ( 'Motorcycle', 'Green' )); 

Update

The fastest query I've found so far is something like this. It takes 100-200 ms, but it seems pretty inflexible and ugly. I basically grab all the products that match Ford , Black or Skateboard . They I combine all the tags for these agreed products in a line separated by a colon, and delete all products that match :Green: And :Motorcycle: Any thoughts?

 SELECT p.productid, Concat(':', Group_concat(alltags.tag SEPARATOR ':'), ':') AS taglist FROM products p JOIN producttags tag1 USING (productid) JOIN producttags alltags USING (productid) WHERE p.active = 1 AND tag1.tag IN ( 'Ford', 'Black', 'Skateboard' ) GROUP BY tag1.productid HAVING ( taglist NOT LIKE '%:Motorcycle:%' AND taglist NOT LIKE '%:Green:%' ); 

Answer 1

I would write an exclusive join without subqueries:

 SELECT p.productid FROM products p INNER JOIN producttags AS t ON p.productid = t.productid LEFT OUTER JOIN producttags AS x ON p.productid = x.productid AND x.tag IN ('Motorcycle', 'Green') WHERE p.active = 1 AND t.tag IN ( 'Ford', 'Black', 'Skateboard' ) AND x.productid IS NULL; 

Make sure you have an index for products in two columns (active, productid) in that order.

You should also have a pointer to producttags above the two columns (productid, tag) in that order.

Another question I need to do is something like everyone else (Car) or (skateboard) or (green and motorcycle) or (red and motorcycle).

Sometimes these difficult conditions are difficult for the MySQL optimizer. One common way is to use UNION to combine simpler queries:

 SELECT p.productid FROM products p INNER JOIN producttags AS t1 ON p.productid = t1.productid WHERE p.active = 1 AND t1.tag IN ('Car', 'Skateboard') UNION ALL SELECT p.productid FROM products p INNER JOIN producttags AS t1 ON p.productid = t1.productid INNER JOIN producttags AS t2 ON p.productid = t2.productid WHERE p.active = 1 AND t1.tag IN ('Motorcycle') AND t2.tag IN ('Green', 'Red'); 

PS: Your tag table is not an attribute table.

Answer 2

I would get all the unique ID matches and unique identifiers for filtering, then LEFT JOIN in these lists (like tigeryan) and filter out any IDs that match. The request should also be easier to read and modify, keeping all requests separate. It should also be pretty fast, although it may not look.

 SELECT * FROM products p WHERE p.active=1 AND productid IN ( SELECT matches.productid FROM ( SELECT DISTINCT productid FROM producttags WHERE tag IN ('Ford','Green','Skatebaord') ) AS matches LEFT JOIN ( SELECT DISTINCT productid FROM producttags WHERE tag IN ('Motorcycles','Green') ) AS filter ON filter.productid=matches.productid WHERE filter.productid IS NULL ) 

Sometimes a JOIN is faster than an IN, depending on how mysql optimizes the query:

 SELECT p.* FROM ( SELECT matches.productid FROM ( SELECT DISTINCT productid FROM producttags WHERE tag IN ('Ford','Green','Skatebaord') ) AS matches LEFT JOIN ( SELECT DISTINCT productid FROM producttags WHERE tag IN ('Motorcycles','Green') ) AS filter ON filter.productid=matches.productid WHERE filter.productid IS NULL ) AS idfilter JOIN products p ON p.productid=idfilter.productid AND p.active=1 

The second query must force the join order, since you must first perform internal selections.

Answer 3

I would usually attack this, trying to destroy entries from ...

 select p.productid from product p left join producttags tag1 on p.productid = tag1.productid and tag1.tag NOT IN ('Motorcycles','Green') where tag1.tag IN ('Ford','Black','Skateboard') and p.active = 1 

Answer 4

How about this:

 SELECT DISTINCT p.id FROM products AS p JOIN producttags AS included ON ( included.productid = p.id AND included.tag IN ('Ford', 'Black', 'Skatebaord') ) WHERE active = 1 AND p.id NOT IN ( SELECT DISTINCT productid FROM producttags WHERE tag IN ('Motorcycle', 'Green') )