как работает WHERE EXISTS в MySQL


Нашёл у себя в коде запрос с WHERE EXISTS и решил разобраться, как оно работает.
Втыкал-то я его когда-то копи-пастом, толком не разобравшись.
А тут решил выяснить. Я в последнее время все запросы для себя стараюсь прояснять.

Стал читать доку, но как-то не въехал. А параллельно профилировал все варианты получения того же самого.

Сам запрос такой
SELECT SQL_NO_CACHE n.idtitledate_pub FROM news n WHERE NOT EXISTS
    
(SELECT FROM categories c WHERE c.category AND c.id=n.id )
    
ORDER BY n.date_pub DESC limit 10


включил профилировние
set profiling=1;
выполнил запрос, и запросил результат профайлинга, который выдал мне таблицу, которая многое проясняет:
show profile;
+----------------------+----------+
Status               Duration |
+----------------------+----------+
starting             0.000077 |
checking permissions 0.000004 |
checking permissions 0.000003 |
Opening tables       0.000023 |
System lock          0.000009 |
init                 0.000030 |
optimizing           0.000009 |
statistics           0.000094 |
preparing            0.000058 |
executing            0.000003 |
Sorting result       0.000007 |
Sending data         0.000030 |
optimizing           0.000010 |
statistics           0.000112 |
preparing            0.000013 |
executing            0.000002 |
Sending data         0.000042 |
executing            0.000002 |
Sending data         0.000028 |
executing            0.000002 |
Sending data         0.000032 |
executing            0.000003 |
Sending data         0.000041 |
executing            0.000002 |
Sending data         0.000020 |
executing            0.000002 |
Sending data         0.000020 |
executing            0.000002 |
Sending data         0.000027 |
executing            0.000002 |
Sending data         0.000021 |
executing            0.000002 |
Sending data         0.000019 |
executing            0.000002 |
Sending data         0.000020 |
executing            0.000002 |
Sending data         0.000024 |
end                  0.000004 |
query end            0.000003 |
closing tables       0.000006 |
freeing items        0.000020 |
logging slow query   0.000002 |
cleaning up          0.000003 |
+----------------------+----------+
43 rows in set (0.00 sec)


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

Предположение наше подкрепляется тем, что если в выдаче не встретилось категорий-исключений, то этих подзапросов выполняется 10 штук. Но если втречается такая категория, то количество подзапросов увеличивается: в приведённом примере их 11 - одна новость попала под фильтр, и вместо неё пришлось прочитать ещё одну.

То есть, это получается такой джойн для бедных, который, при этом, работает в 100 раз лучше запроса
WHERE id IN (SELECT id FROM table)
Но использовать его имеет смысл только с лимитированными запросами, поскольку если идёт большая выборка, то эти подзапросы, пусть даже и супербыстрые, в сумме всё равно дают ощутимое время выполнения.

Так что в итоге я всё равно отказался от WHERE EXISTS и сделал нормальный джойн,
SELECT FROM news n LEFT JOIN categories c ON n.id c.id and c.category 5 WHERE c.id is null
который можно использовать как для получения самих новостей, так и их количества
(спасибо MiksIr с форума, который натыкал меня носом в идиотский тупняк с этим запросом)

Кстати, супербыстрота этих подзапросов объясняет эффективность handlerSocket-а: эти самые миллионные доли секунды из профайлинга - и есть реальное время обращения к storage engine, а остальное - это накладные расходы SQL-я...