Запрос mysql one-to-many с отрицанием и/или несколькими критериями

Я думал, что такой запрос будет довольно легким из-за природы реляционных баз данных, но, похоже, мне пригодится. Я также искал вокруг, но ничего не нашел, что действительно помогло. Здесь ситуация:

Скажем, у меня простая связь для продуктов и тегов продуктов. Это отношение "один ко многим", поэтому мы можем иметь следующее:

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

Какой самый эффективный способ для запроса для всех (Ford OR Black OR Skateboard) AND NOT (Motorcycles OR Green)? Другой вопрос, который мне нужно сделать, это что-то вроде (Car) or (Skateboard) or (Green AND Motorcycle) or (Red AND Motorcycle).

В таблице продуктов содержится около 150 тыс. записей в таблице продуктов и 600 тыс. записей в таблицах тегов, поэтому запрос должен быть максимально эффективным. Здесь один запрос, с которым я возился (пример № 1), но, похоже, занимает около 4 секунд или около того. Любая помощь будет высоко оценена.

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

Самый быстрый запрос, который я нашел до сих пор, примерно такой. Он принимает 100-200 мс, но кажется довольно негибким и уродливым. В основном я хватаю все продукты, которые соответствуют Ford, Black или Skateboard. Их я объединяю все теги для этих согласованных продуктов в строку, разделенную двоеточием, и удаляю все продукты, которые соответствуют :Green: И :Motorcycle:. Любые мысли?

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:%' );
4 ответа

Я бы написал исключающее соединение без подзапросов:

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;

Убедитесь, что у вас есть индекс для продуктов по двум столбцам (active, productid) в этом порядке.

У вас также должен быть указатель на producttags над двумя столбцами (productid, tag) в этом порядке.

Еще один вопрос, который мне нужно сделать, это что-то вроде всех (Car) или (скейтборд) или (зеленый и мотоцикл) или (красный и мотоцикл).

Иногда эти сложные условия трудны для оптимизатора MySQL. Одним из распространенных способов является использование UNION для объединения более простых запросов:

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: Ваша таблица тегов не является таблицей атрибутов-атрибутов.


Я бы получил все уникальные совпадения ID и уникальные идентификаторы для фильтрации, затем LEFT JOIN в этих списках (как и tigeryan) и отфильтровать любые ID, которые соответствуют. Запрос также должен быть легче читать и изменять, сохраняя все запросы отдельно. Он также должен быть довольно быстрым, хотя он может и не выглядеть.

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
)

Иногда JOIN быстрее, чем IN, в зависимости от того, как mysql оптимизирует запрос:

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

Второй запрос должен принудительно выполнить порядок соединения, так как сначала необходимо выполнить внутренние выборки.


Я бы обычно атаковал это, пытаясь уничтожить записи из...

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


Как насчет этого:

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')
)

licensed under cc by-sa 3.0 with attribution.