Ну, и что, до сих пор никто не нашел ошибку sTa1kEr? Тогда позвольте мне....

Обратите внимание на вот эту строчку:


Откуда у нас взялась таблица table? Мы ее не создавали! У нас есть только tmp (временная) и test (исходная). В результате этот запрос всегда НЕ ВЫПОЛНЯЕТСЯ, что создает ИЛЛЮЗИЮ того, что эта сумасшедшая комбинация действительно быстрее, чем IN конструкция...
Если исправить этот table на test, как и должно быть, результаты теста будут совсем другими: 


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

Мой вариант PHP файла, на котором проводился тест, с использованием стандартных функций MySQL:


Это сообщение отредактировал(а) FractalizeR - 15.1.2008, 13:57

Хм, видимо придется поднять старую темку.

Во-первых, FractalizeRу плюс за глазастость!

Но ситуация несколько сложнее. Я тестировал оба варианта (с mysqli и mysql_xxx) на двух машинах - стареньком Duron-1200 с 512 "мозгами" (одной планкой) и ноутбуке с Core2Duo T5500 (1.66 ГГц) и гигагабайтом памяти (двухканальной), оба под WinXP (к сожалению). Результаты вот:

Duron-1200:
Core2Duo T5500:
Поражают как тормоза варианта с IN на старой одноядерной машине, так и быстрота варианта с джойном (без принудительного удаления времянки) на ней же. При хорошем запасе памяти и ресурсов проца, безусловно, вариант с IN (особенно при небольшом диапазоне) выглядит куда лучше, но вариант с времянкой, похоже, менее зависим от этих "мелочей". Надо бы протестить в более реальных условиях - под *NIX, под нагрузкой...

Добавлено через 5 минут и 34 секунды

Это не класс, это улучшенное (по сравнению со старой php_mysql) расширение PHP5. Поддерживаются несколько запросов за вызов и новые возможности самой базы, но "волшебства" для перехвата ошибок в нем, увы, нет.

Меня вообще-то поражает не это.


Тестирование 1.000 тысячи записей с IN заняло 11.9 секунд, а тот же самый тест с IN но для 10.000 (в 10 раз больше) записей занял 1.6 секунды (в 10 раз меньше). Вам это не кажется странным?
В общем, я думаю, вы просто что-то сделали неправильно. IN со скалярными значениями всегда быстрее любых других вариантов должен быть.

Это сообщение отредактировал(а) FractalizeR - 16.1.2008, 00:47

Не в 10, а в 7, если быть совсем точным . Нет, не удивляет - объем данных, перелопаченных сервером, в 10 раз меньше, как ни крути...


Не могу за это поручиться, когда речь идет о MySQL - у нее, насколько я помню, "наследственные проблемы" с оптимизацией множественных OR в условии WHERE (а IN, насколько я понимаю - частный случай этого по сути). В каких-то версиях доходило до того, что UNION отдельных запросов с единственным значением в каждом оказывался быстрее. Нужно будет протестить как следует - чисто базу, без PHP, посмотреть EXPLAIN, желательно под никсами... постараюсь сделать ближе к вечеру, если кто сделает раньше/параллельно - буду только рад.

Не в 10 раз меньше, а в 10 раз больше. Посмотрите внимательно на результаты теста. Тысяча строк отобрана за двенадчать секунд, а десять тысяч - за две секунды!


Почитайте мануал MySQL по использованию конструкции IN и вам все станет понятно.

Но 10 тыс. отобраны из одного миллиона, а 1 тыс. - из десяти миллионов! Посмотрите внимательно на условия теста ;). Есть же разница для сервера, имхо...


Вот относительно недавний FAQ, первый же вопрос которого полностью совпадает с тем, что некогда крепко засело в моем мозгу (причем автор утверждает, что и ORы, и IN с константами используют бинарный поиск - судя по комментам здесь, начиная с 4-й версии). Но ведь все равно, насколько я понимаю, этот бинарный поиск будет делаться для каждой строки исходной таблицы...

Насчет UNOIN'а, признаю, прогнал (судя по тем же комментам).

Количество строк в таблице никто не менял. Откуда там один миллион и десять миллионов? Просто опять же автор теста допустил ошибку и тут. А я ее за ним скопировал. Нолик не дописан.


О том, как работает IN написано в мануале по MySQL.

О ветке 3.x MySQL давно уже пора забыть. 

Это сообщение отредактировал(а) FractalizeR - 16.1.2008, 19:07

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


Читал. Сортирует, потом бинарный поиск. Написано, что это very quick, если в списке одни константы одного типа. Но вряд ли при этом предполагаются десятитысячные диапазонища... 


Пора, не спорю. Не всегда получается ;)

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

1. Количество строк действительно было разное, что я и написал в посте.
2. С корректными запросами я точно проводил тест.
3. Это было неоднократно замечено и испробовано на рабочих, реальных данных. Причем не только в MySQL но и в MSSQL, результат один - IN всегда проигрывает временной таблице, хранящейся в памяти(важно, что именно в памяти, т.к. можно и на диске создать временную). А на особо сложных запросах, это настолько особо критично.

Хотелось бы, чтобы вы привели образцы скриптов, где IN медленнее, чтобы я смог провести тестирование на своем компьютере. Мне с трудом верится, что конструкция IN может быть настолько неоптимальной. Конечно, в MySQL 3.23 ее использование растягивалось в цепь OR и индекс не использовался. Но 3.23 пора забыть, как страшный сон 

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

Это сообщение отредактировал(а) FractalizeR - 16.1.2008, 20:30

FractalizeR, попробуйте Ваш же скрипт на таблице с еще на порядок большим (100 млн.) числом записей. Свои результаты запощу позже - "для чистоты эксперимента".