SQL запрос на пересечение внутри одной таблицы

может использовать индекс от полнотекстового поиска? он умеет такие запросы эффективно решать

Боюсь, что второй вариант с группировкой намного эффективнее полнотекстового поиска.

нет, если поиск сделан на базе инвертированного индекса (только с ним скорость update-операций потом будет страдать)

SELECT DISTINCT object_id FROM tab t1 WHERE NOT EXISTS (SELECT * FROM tab t2 WHERE keyword_id IN (kw1,kw2,...,kwN) AND NOT EXISTS (SELECT * FROM tab t3 WHERE t3.object_id = t1.object_id AND t3.keyword_id = t2.keyword_id))

на mysql в итоге пришёл ко второму решению, работает достаточно быстро, правда у меня ещё делаетс таблица индекса слов, так что пересечение идёт по int ключу.

если интресно могу схемку написать.

перечитал пост, у тебя тоже самое.

>WHERE NOT EXISTS (SELECT * FROM ...

Это же смерть для MySQL :)

ну определитесь для начала Вам нужно изящно или быстро, если быстро, то без count никак

Однако, всё равно остаются 0,1 сек. и файловая сортировка в explain.

А составной уникальный индекс присутствует ? С ним по идее не должно быть сортировок на файлухе. Да и план запроса стоит привести, время работы оно не показатель.

и файловая сортировка в explain

filesort - это совсем не то, что вы думаете

Формально, можно было бы так:

select d.obj
from
  (select A.[key], B.obj
   from
    (select [key] from T where [key] in (key-list) group by [key]) A,
    (select obj from T group by obj) B
  ) D
  left join T on T.[key] = D.[key] and T.obj = D.obj
where not (T.[key] is null)
group by d.obj
having count(d.obj) > 1

> Однако, всё равно остаются 0,1 сек

Уж извини, но это время на открытие и поиск по индексу, а быстрее чем поиск по индексу здесь ничего придумать нельзя в принципе, так что
1. create index idx_kewords on tablename(keword_id,object_id)
2. select distinct object_id from tablename where keyword_id in (<тут select id from keywords wher kw like '...'> или просто список id'ов ключевых слов>)
3. И не трахай нам моск

>А составной уникальный индекс присутствует ?

Присутствует. И используется.

С ним по идее не должно быть сортировок на файлухе.

Иногда попадаются ситуации, что никак не избавиться. Даже с принудительным указанием адекватных индексов.

...

На самом деле вопрос пока не слишком актуален. Со вторым вариантом суммарное время генерации страницы вышло под 0,2-0,3 сек., страницы не частого посещения. Так что пока потерпит.

>какая версия, кстати?

5.1.50-r1

>>Однако, всё равно остаются 0,1 сек

Уж извини, но это время на открытие и поиск по индексу

Так и у меня не Celeron :) Холодные выборки без filesort и temporary tables выполняются на том же объёме за 10-30мс.

2. select distinct object_id from tablename where keyword_id in

Это совсем не моя задача. Это объединение множеств. А мне нужно - пересечение.

Ну должно тогда сработать... Кстати, в сравнении с твоим результатом, что дало (если пробовал, конечно)? А то у меня на 14тыс записей чото «около одновременно» было.

>Кстати, в сравнении с твоим результатом, что дало (если пробовал, конечно)?

Пока не пробовал. Удалённая машина, лениво коннектиться только для теста, и запрос сильно переписывать надо (я задачу немного упростил, чтобы в частности не закапываться).

А то у меня на 14тыс записей чото «около одновременно» было.

У меня - 230 тыс. привязок :) 15 тыс. ключевых слов (и фраз) и 151 тыс. объектов :)

> файловая сортировка

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

Какая, к черту, файловая сортировка? filesort - это когда mysql не может выполнить сортировку исопользуя существующие индексы и сортирует, так сказать, «своими руками», т.е. в процессе выполнения запроса. Кажется, там в текущей реализации quicksort применяется.

Иногда, это, действительно может привести к сбрасыванию данных на диск, как, впрочем, и при любой другой операции с временными таблицами, так что к файлам filesort напрямую никакого отношения не имеет.

В данном случае сортировка происходит в результате обработки 'group by'.

>Какая, к черту, файловая сортировка?

Всего лишь буквальный перевод. Понятный всем, кто сталкивался с mysql. Есть более правильный официальный перевод? Поделитесь!

В данном случае сортировка происходит в результате обработки 'group by'.

Нет. В результате сортировки по группировке. Это немного иное.

Насколько я помню, нигде это определение в официальных доках не раскрывается, - я, кажется, вычитал его на mysqlperformanceblog. К файлам это действительно отношения прямого совершенно точно не имеет (по крайней мере в современных версиях mysql!), так что, вероятнее всего, название сохранилось с тех пор, когда оно имело реальный смысл.

Нет. В результате сортировки по группировке. Это немного иное.

Скорее всего, мы говорим об одном и том же, просто разными словами, а я просто недостаточно точно выразился. Я имел в виду примерно следующее, если развернуто:

При в ходе обработки алгоритма группировки (т.е. group by), mysql сортирует resultset в соответствии с полями, указанными в 'group by'; чтобы строки которые агрегируются «шли подряд», - потом к полям, по которым 'group by' указано не было, применяется агрегирующая функция, если такая была указана в 'select' (например, sum() или, там, group_concat()). Таким образом, 'group by' неявным образом приводит к сортировке.

Фух, прошу прощения за инглиш.

Пойду, что ли, зарегистрируюсь.