Скорость поиска: PHP vs MySQL

В данном случае ставлю на БД. Но узнать результат все равно интересно.

Ставлю на эластик.

БД без вариантов, только не родной мускульный where TEXT like %term%, а родной мускульный fulltext search

ставлю на php, в частности 7.3

Кстати, а откуда данные читаем? php скрипт будет читать из mysql, или у него данные уже в памяти?

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

Мне кажется это очевидно вопрос был в скорости БД vs PHP.

$result = mysqli_query($link, "SELECT id from table where content like '%test123%'"));
        while($row = mysqli_fetch_array($result)){
	$id=$row['id'];
        echo "$id";
} 

Vs

$result = mysqli_query($link, "SELECT id, content from table"));
        while($row = mysqli_fetch_array($result)){
	$id=$row['id'];
        $content=$row['content'];
        if (substr_count($content,"test123")>0) {echo "$id";}
} 

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

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

За совет спасибо, надо почитать.

Субд уделает пых без вариантов. Чтобы сделать substr_count нужно сперва данные получить пыхом, потом распарсить пыхом в массив. А в mysql еще есть fulltext index и всё такое.

... SELECT id from table where content like '%test123%' ...
PHP в обоих случаях будет читать из мускуля, только в первом варианте where обрабатывается логикой самого мускуля, а во втором варианте - логикой пыха.

Тогда логичнее использовать СУБД-шный запрос - будет экономия за счет меньшей передачи данных по соединению между MySQL и PHP.
Но поскольку критерий выборки like '%test123%' - поиск по подстроке в серединке поля - то внутри СУБД-шки по идее будет использоваться последовательное сканирование таблицы. Просто посмотри план выполнения SQL-запроса - и всё станет ясно.

Если реально ты будешь искать слова, а не произвольные подстрочки, то можно задействоавать СУБД-шный полнотекстовый поиск - тогда поиск внутри СУБД-шки будет пошустрее. Но это требует создания соответствующих индексов и использования других SQL-конструкций.

... ну и сканирование таблицы СУБД-шка в любом случае выполнит пошустрее чем PHP-код.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

Не?

А я ставлю на SphinxSearch :) Конечно, ещё Elastic есть, но он ресурсов жрёт совсем много.

20 миллионов записей по килобайту — это 20-50Гб, в зависимости от кодировки, индексов и т.п. И всё это для скорости надо держать в памяти… Так что лучше уж специализированные поисковые движки :)

Лорчую.

пока там пхп че то найдет, успеешь состариться

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

удваиваю эластик

TEXT (text) с максимальным размером в несколько килобайт. Записей может быть 10-20 миллионов

Т.е. примерно 10-20Гб? А не жирно ли на каждый запрос столько пропихивать в память? Сам себя решил заDDOSить, видимо.

А я ставлю на SphinxSearch

+1. Если правильно настроить правила поиска и индексации текста - все будет летать.

На самом деле, ТС сформулировал задачу весьма нечетко - он не сказал, что конкретно он собирается искать )
Допустим, к примеру, что ему нужно искать всего одно слово. Не факт, что специализированные поисковые движки смогут в этом случае переплюнуть встроенные возможности СУБД-шки при выделении одних и тех же вычислительных ресурсов (память, процессор) и правильной настройке.

Ставлю на БД, особенно если у вас актуальная версия мускуля. Вычитка всех записей и поиск по ним в php - дело не быстрое.

А вообще для поиска есть специальные решения вроде sphinx или solr. Если же сравнивать нужно обязательно в php-коде, то лучше периодически считывать всю выборку в memcashe/redis, и обращаться к ним, когда нужно что-то найти среди полученного ранее массива записей. Вообще, все жирные операции с выборками из БД хорошо при случае кэшировать, что-бы не дёргать базу каждый раз длинными запросами.

Да конечно БД. Порвёт пхп как тузик грелку.

Да конечно БД. Порвёт пхп как тузик грелку.

Да это-то понятно. Более интересный вопрос: в каких случаях специализированные решения (elasticsearch, sphinx и пр.) действительно дают существенный выигрыш по сравнению со встроенным fulltext-поиском СУБД-шки без выделения дополнительных вычислительных ресурсов. Конечно, если сначала померить скорость родного СУБД-шного поиска на одном серваке, а потом сверху навесить кластер elasticsearch, то результат будет пошустрее )
А вот если всё крутить на одном серваке и вопрос в том, куда отдать оперативную память - под кэш MySQL или под дополнительный специализированный сервис?

под кэш MySQL

Оно кеширует результаты запросов. А запросы поиска предполагаются разными, люди же что-то ищут. На такое кеша не напасёшься. Да и протухнуть может. Конечно лучше отдать память доп сервису, он же деревья вроде как строит. Не?

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

Нет, СУБД-шный кэш содержит данные - таблицы и индексы. Чем больше кэш - тем шустрее работа (меньше ввода-вывода). «Деревья» (индексы для fulltext-поиска) имеются и там и там. Собственно, вопрос в том, где и в каких случаях эти самые «деревья» работают более шустро.

СУБД-шный кэш содержит данные - таблицы и индексы

А я о чём? Ну да, таблица с MEMORY энжином или вьюха — результат запроса.

Чтобы случился кеш, надо запрос выполнить. А кеш ещё и протухать умеет, т.е. снова запрос делать придётся.

А какие оно там индексы будет содержать при куче джойнов и подзапросов?

Короче я за спецсредства.

Чтобы случился кеш, надо запрос выполнить...

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

не протухают

Протухают. И по времени и по причине того, что где-то что-то добавилось-обновилось-удалилось.

если база размером всего

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

индекс спец-сервиса тоже надо в память загрузить

Но там не реляшка, а готовые деревья, что быстрее.

postgres + RUM-индексы

Вы не путайте «кэш запросов» и «кэш табличных данных» (где просто лежат страницы таблиц и индексов - innodb_buffer_pool_size). Я говорю о последнем. Если что-то добавилось-обновилось-удалилось, то индекс спец-сервиса тоже нужно обновлять.

Но там не реляшка, а готовые деревья, что быстрее

А индекс «реляшки» - это что по вашему???

Я вообще-то о кеше результатов запроса. Там уже никаких индексов и деревьев нет. Остальные кеши вторичны, т.к. пользователя интересует результат, и результат надо получить, а без кеша результата будет больший ио. Да, можно натюнить всякого. Но зачем? А вдруг натюнив одно, провалим другое? Есть более подходящие инструменты. Мускуль строит индексы. Если речь о простых данных в полях, то да. Если о тексте, то эффективность индексов в хлам падает уже при чар 255.

Я вообще-то о кеше результатов запроса. Там уже никаких индексов и деревьев нет. Остальные кеши вторичны, т.к. пользователя интересует результат, и результат надо получить, а без кеша результата будет больший ио.

Абсолютно неверное рассуждение. Если вся база полностью размещается в кэше данных, то при SELECT-запросах не будет никаких обращений к дискам. Кэш результатов запроса - это вспомогательный инструмент. Основная память должна выделяться под кэш данных.

Мускуль строит индексы. Если речь о простых данных в полях, то да. Если о тексте ...

Мы здесь говорим не об обычных индексах, а о специализированных - предназначенных именно для fulltext-поиска (такие есть и в СУБД-шках). Поскольку реализация и состав индексов бывают разными, то и вопрос: какой индекс в каких случаях срабатывает более быстро, а когда разницы особой нет?

Ты сначала создай файл на 20 лямов строк и попробуй просто прочитать его пыхом.

неверное рассуждение

Допустим.

вся база полностью

А в спецсредствах не вся, а только словарь. Уже плюс, да? Да и грузить всю БД в память — то еще извращенье. Ну если только уже совсем никак.

не об обычных индексах

Спецсредства на то и спец, что по максимуму затюнены под задачу, в отличии от.

https://stackoverflow.com/questions/13246597/how-to-read-a-large-file-line-by...

Да с пыхом тема уже закрыта (всё очевидно) - интереснее поговорить на сопутствующую тему...

А в спецсредствах не вся, а только словарь. Уже плюс, да? Да и грузить всю БД в память — то еще извращенье. Ну если только уже совсем никак.

Ну, всё держать в памяти не обязательно - СУБД-шка тоже не совсем глупая и держит в кэше то, что чаще используется. Ну и я ведь вопрос как поставил... Допустим у вас БД не такая уж и большая (какой-нибудь магазин на гигабайт) - оно вам надо поднимать на сервере лишний сервис и иметь лишнюю суету с его поддержкой? Может лучше просто правильную СУБД-шку выбрать с быстрым встроенным полнотекстовым поиском?

Спецсредства на то и спец, что по максимуму затюнены под задачу, в отличии от

Ну, если продолжить - ... в отличии от СУБД-шных спец-индексов, по максимуму затюненых на задачу полнотекстового поиска - то как-то становится не всё так очевидно.

Тогда, чтобы мы тут не переливали из пустого в порожнее — нужны тесты.

Ну, в общем-то, да )

PHP vs. MySQL

Заканчивался 2018 год, сцука

PHP vs. MySQL

Заканчивался 2018 год, сцука

)) А чего ж ты хочешь? Там, где HR-ам поручают спецов искать, еще и не такое будет... Нонче же ищут скилы, строчки в резюме, а не мозги.

Не не. Я не HR и не спец. Просто для себя на коленке провожу эксперименты и смотрю в каком направлении копать.

Спасибо всем кто советует хорошее :)