Оптимизировать SQL-запрос для сопоставления тегов

Пример набора данных:

id | tag
---|------
1  | car
1  | bike
2  | boat
2  | bike
3  | plane
3  | car

id и tag проиндексированы.

Я пытаюсь получить идентификатор, соответствующий тегам [автомобиль, велосипед] (количество тегов может варьироваться).

Наивный запрос для этого был бы таким:

SELECT id
FROM test
WHERE tag = 'car'
    OR tag =  'bike'
GROUP BY id
HAVING COUNT(*) = 2

Однако это довольно неэффективно из-за группы по и того факта, что любая строка, соответствующая одному тегу, учитывается для группы по (а у меня большой объем).

Есть ли более эффективный запрос для этой ситуации?

Единственное решение, которое я вижу, - это иметь другую таблицу, содержащую что-то вроде:

id | hash
---|------
1  | car,bike
2  | boat,bike
3  | plane,car

Но это решение непросто реализовать и поддерживать в актуальном состоянии.

Дополнительная информация:

  • соответствие имен должно быть точным (без полнотекстового индекса)
  • количество тегов не всегда 2

mysql sql optimization query-optimization sql-optimization
person Matthieu Napoli    schedule 08.10.2012    source источник
comment
Хорошая постановка вашего вопроса. С примером SQLFiddle это было бы идеально :)   -  person juergen d    schedule 08.10.2012
comment
Я бы начал с нормализации ваших тегов. У вас должна быть таблица тегов с идентификатором и именем. Тогда ваш набор данных выше будет id, TagID   -  person Tobsey    schedule 08.10.2012
comment
так что в этом случае результатом будет автомобиль и велосипед, потому что у них обоих есть 2 строки с именем?   -  person Diego    schedule 08.10.2012
comment
Ваши опасения неуместны. Ваш наивный вопрос вполне подойдет. Индекс на (tag, id) должен обеспечивать очень хорошую производительность для этого запроса, поскольку его можно удовлетворить только с помощью индекса.   -  person Gordon Linoff    schedule 08.10.2012
comment
@Tobsey Ну, на самом деле это все идентификаторы, но я хотел максимально упростить вопрос и быстро сделать его понятным   -  person Matthieu Napoli    schedule 08.10.2012
comment
@Diego Да, я ищу точное совпадение   -  person Matthieu Napoli    schedule 08.10.2012
comment
У вас в настоящее время есть проблемы с производительностью вашего запроса? Есть ли уникальное ограничение на (id, tag)?   -  person Tim Lehner    schedule 08.10.2012
comment
так что попробуйте мой запрос. Если я правильно понял, он вернет то, что вы ожидаете   -  person Diego    schedule 08.10.2012

Ответы (3)


arrow_upward
0
arrow_downward

попробуй это:

SELECT id
FROM test
WHERE tag in('car','bike')
GROUP BY id
HAVING COUNT(*) = 2

И создайте некластеризованный индекс в столбце тегов

person AnandPhadke    schedule 08.10.2012
comment
IN является синонимом OR. Это не имеет значения. - person podiluska; 08.10.2012
comment
Практически IN быстрее OR - person AnandPhadke; 08.10.2012
comment
Я действительно не думал о IN, есть ли у кого-нибудь из вас ссылка для поддержки того или иного варианта (быстрее или нет)? - person Matthieu Napoli; 08.10.2012
comment
проверьте эту ссылку: stackoverflow.com/questions/ 1013797 /. Здесь прочтите все ответы, и я также предлагаю использовать временную таблицу в случае огромного количества аргументов в предложении IN, но здесь я не думаю, что у вас будет огромное количество тегов. - person AnandPhadke; 08.10.2012

arrow_upward
0
arrow_downward

Ну вот:

select id from TEST where tag = 'car' and ID in (select id from TEST where tag='bike')
person Romo    schedule 08.10.2012
comment
Из ОП: количество тегов не всегда 2 - person Tim Lehner; 08.10.2012
comment
Да, но вы можете расширить запрос, добавив в него больше идентификаторов. Вам уже нужно знать, сколько элементов искать в запросе. И в этом примере вы можете использовать чистый индекс без какой-либо группы по и с подсчетом. - person Romo; 08.10.2012
comment
Хорошо, чтобы проголосовать против, но, пожалуйста, проанализируйте запрос по сравнению с другими примерами в ответах. Вы увидите, что это намного быстрее. И независимо от того, как вы это делаете, вам все равно нужно построить запрос о том, сколько тегов (автомобиль, велосипед и т. Д.) Содержится в запросе. - person Romo; 09.10.2012

arrow_upward
-1
arrow_downward

не уверен, что я вас понял, но попробуйте следующее:

select tag, count(*)  as amount
into #temp
from MYTABLE
group by tag


select t1.tag 
from #temp t1 join #temp t2 on t1.amount=t2.amount and t1.tag=t2.tag and t1.amount=2

должен привести велосипед и автомобиль, так как они оба имеют 2 ряда, что равно 2

person Diego    schedule 08.10.2012
comment
Я пытаюсь оптимизировать запрос, ваш способ работы не кажется более эффективным? - person Matthieu Napoli; 09.10.2012
comment
Думаю, стоит попробовать и сравнить планы. - person Diego; 09.10.2012