Использование mysql функций в Sphinx

Мигрируя с одного сервера на другой, я увидел mysql таблицу с кешем некоторых данных, которая занимала ~ 500 Гб. Вспомнив, что давно хотел прикрутить поиск по кешу с помощью Sphinx решил не откладывать и приступил к делу..

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

Для начала, в таблице кеша данные были сериализированны, т.е. были примерно в таком формате:

понятное дело, что это не совсем подходит для поиска. Конечно, можно и по такому полю делать поиск, но мне нужен был поиск именно по обычному тексту: title + description. Кроме того, нужно было исключить тег <strong> из текста, иначе он бы мешал при поиске.

Насколько мне известно, mysql не умеет делать unserialize строки как и strip_tags , что вполне логично. В связи с этим, у меня был выбор: написать плагин для mysql либо попробовать обойтись строковыми функциями, я выбрал второй вариант.

Пока что, сосредотачиваемся на выборке данных и забываем про Sphinx.

Разбиваем задачу на части:

• Сделать десериализацию для определенных значений массива (у меня это titleHL и descHL)
• Вырезать определенные теги
• Склеить нужные значения (titleHL+descHL) и на выходе получить выборку вида: id, content

Открываем любой редактор позволяющий выполнять SQL и начинаем писать. У меня получился такой код:

вот для такой таблицы:

Следующее, что нужно сделать - это перенести всё это в конфигурацию Sphinx-а.

Все достаточно просто, у Sphinx-а, есть два конфигурационных параметра: sql_query_pre и sql_query_post, с их помощью мы и объявляем все то, что мы подготовили.

Код конфига будет чуть ниже, а я расскажу про проблему которая и послужила толчком к написанию этой статьи. Итак, написав и отладив код в SQL редакторе, я перенес его в конфиг Sphinx-а. Однако при попытке запуска индексации:

я получил ошибку:

MySQL не хотела принимать DELIMITER не под каким соусом. Я долго тупил и искал в чем же проблема. Как оказалось все достаточно просто и это не проблема Sphinx-а, как я полагал увидев ошибку.  Вот ответ c stackoverflow на вопрос: How to execute mysql command DELIMITER, который мне все объяснил:

You probably don't need to change the delimiter.

The delimiter is needed in the CLI to tell where the SQL statement ends, because the CLI is going to keep reading and executing more statements until you tell it to stop (e.g., with exit or Control-D). But what it actually reads is just a stream of characters; it somehow needs to figure out where one statement ends and the next starts. That's what the delimiter does.

In PHP, each function call executes one statement. There can't be multiple statements in one function call, so there is no need for a way to delimit them. The statement is the entire string. This is true of the old mysql_query as well as the newer mysqli_query and PDO. Of course, there is mysqli_multi_query if you really want to pass multiple queries to one function.

In the case of a stored procedure/trigger/function/etc., there can be multiple statements, but that's handled by MySQL itself (and is always ;, AFAIK). So as far as PHP is concerned, that's still one statement.

The delimiter setting you're seeing in phpMyAdmin is probably being used to split statements apart, and is probably being done in PHP code. It has to do this because it is accepting user input consisting of multiple statements, but must pass only one statement per function call. (I haven't checked the phpMyAdmin code to be completely sure of this).

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

В итоге, я удалил указание DELIMITER-а в запросах, в конфиге и тогда всё заработало. Вот так те же запросы выглядят в конфиге Sphinx-a:

Тут удалять функции в sql_query_pre не обязательно, т.к. они удаляются в конце, но я решил все таки оставить, чтобы пример для отладки был более полный.

После этого выполняем индексацию:

И можем проверять поиск:

Очередная задача решена 🙂