Есть 2 запроса, каждый из которых возвращает множество идентификаторов (по 3000 штук)
Нужно найти их пересечение.

Просто-напросто эти 2 запроса я вначале объединял через INNER JOIN как положено с использованием индексов. Такой запрос выполняется не очень быстро. Все варианты с разными индексами и разными комбинациями условий испробовал - результат не устраивает.

В то же время каждый из подзапросов отдельно выполняется в 10 раз быстрее. Вот и хочу последовательно их выполнить и найти пересечение. Можно это сделать и на уровне php, но на уровне MySQL должно быть быстрее (т.к. нет лишней передачи данных между сервером и клиентом + выполняется скомпилированный MySQL-код - быстрее интерпретируемого php)

Попробовал так:


Но такой вариант выполняется медленнее, чем первоначальный (т.к. индексов в этом запросе нет, то перебираются каждый с каждым).

Т.е. здесь нужно получать НЕ декартово произведение, а выполнить элементарную арифметическую операцию со списками (пересечение списков).

Если сделать так:

то MySQL начинает выполнять QUERY2 для каждой записи из QUERY1

MySQL настолько туп, что не может определить, что запрос QUERY2 никак не связан с внешним запросом QUERY1 ?

Т.е. мне вообще непонятно, как в MySQL:
а) ОДНОКРАТНО выполнить подзапрос (который никак не связан с основным запросом)
б) проверить вхождение идентификатора записи из основного запроса в множество идентификаторов, возвращённых вложенным подзапросом
в) при этом скорость выполнения такого запроса должна быть приблизительно такой же (разве что чуть-чуть больше), что и отдельное последовательное выполнение этих запросов

tanglir

Этот вариант я уже прокомментировал:
авторПопробовал так:

Но такой вариант выполняется медленнее, чем первоначальный (т.к. индексов в этом запросе нет, то перебираются каждый с каждым).

авторМне кажется это вообще не реально. Сама выборка гораздо менее затратная штука, чем последующая связка.
Как мы сами убедились, на уровне декартовых множеств связка выполняется слишком медленно (очевидно, это связано с тем, что работа идёт со строками, а не с числами + обусловлено декартовой логикой).

Посему нужно вычислять не декартово произведение, а арифметическое. Арифметическое произведение числовых списков.
Либо заставить MySQL выполнять независимый вложенный запрос в WHERE только 1 раз

авторне должен он так делать,
что то тут не так Именно так и происходит. Проверил в режиме профилирования

авторЕсли query1.id уникальный, то вашу задачу можно решить через UNION ALL + group byТочно. А ведь сам не догадался... Только в Вашем варианте ещё having нужно будет использовать.
Завтра протестирую этот вариант и сравню со своим (свой вариант я тоже придумал и тоже на UNION). Отпишусь.

авторВообще - различает. Баг касается только конструкции IN (SELECT ... )Если только этой конструкции, тогда её нужно переконструировать так, чтобы логика осталась прежней, но конструкция - иной (в которой глюк не наблюдается).

Результат таков:

1) Классический вариант с INNER JOIN и с использованием наиболее подходящих индексов: 0.08 сек
2) Вариант с IN (SELECT) в версии MySQL до 5.6: 15 сек (подзапрос выполняется много раз - баг MySQL)
3) Вариант с EXISTS (коррелированный подзапрос): 15 сек

4) Мой вариант (получение 2 наборов идентификаторов - на уровне SQL, вычисление пересечения - на уровне php):



Здесь запрос выполняется = 0.0238-0.241 сек, [explode + intersect] = 0.0050-0.0052 сек. Итого: 0.0289-0.0294 сек

5) Вариант, предложенный biwed.ru :

Здесь обязательно:
а) должны присутствовать DISTINCT (чтобы в пределах одной группы идентификаторов нельзя было набрать [intersect = 2])
б) должен быть UNION ALL (чтобы сохранить все идентификаторы, присутствующие в обоих выборках)

Такой запрос выполняется 0.0277-0.283 сек , что на 0.001 сек быстрее предыдущего варианта (вывод достоверен - тестировал много раз).

Причём если во второй выборке не указывать имена полей [AS id] и [AS intersect] (во второй выборке они не обязательны), то запрос будет выполняться на 0.0005 сек медленнее и соответственно, выигрыш по сравнению с предыдущим вариантом составит 0.0005 сек .

------------------------------------------------------------------------------------------------------
6) Вариант с IN (SELECT) в версии MySQL 5.6 и выше: ??? сек
Скорее всего, этот вариант сработает быстрее всех предложенных.

авторТакой запрос выполняется 0.0277-0.283 сек
Опечатка. Должно быть так: 0.0277-0. 0 283 сек

biwed.ruDISTINCT можно убрать, переписав запрос. Получится как-то так:
Данная модификация (без DISTINCT) ускоряет запрос ещё на 0.0015 сек .