@NRO

Как оптимизировать SQL запрос рандомной выборки?

Вот что есть: SELECT * FROM dump WHERE text LIKE 'some text' ORDER BY RAND() LIMIT 10

Проблема такова: в таблице на данный момент 600к записей, запрос выполняется в среднем 2-4 секунды, а таблица пополняется, и в перспективе будет раз в 40 больше (что по-логике вещей пропорционально увеличит время запроса и ресурсы на вычисление). В итоге сервак прикурит. Перефразирую задачу: мне нужно выбрать 10 рандомных записей соответствующих паттерну самым оптимальным способом. Помогите пожалуйста.

На хабре вычитал следующее (пример был без LIKE): В процессе выполнения этого запроса MySQL записывает во временную таблицу все (!!!) строки исходной таблицы, с одним новым полем, в которое записываются результаты функции RAND () — т.е. набор произвольных значений. Затем эта временная таблица сортируется filesort по добавленному полю с произвольными значениями и далее выбираются первые 10 записей.
  • Вопрос задан
  • 3216 просмотров
Пригласить эксперта
Ответы на вопрос 4
DmitriyEntelis
@DmitriyEntelis
Думаю за деньги
У Вас есть 2 задачи: задача найти вхождения строки, и задача выбрать из результатов случайные 10.
Это 2 разные задачи :)

Первую задачу логично решать используя инструменты предназначенные для поиска, например Sphinx. sphinxsearch.com
Он быстро вернет Вам id подходящих записей.
Из них средставами PHP выбираем сколько надо случайных, далее запрос в SQL select ... where id in (1,2,3,4...)

В Вашем случае это будет наиболее производительное и масштабируемое решение.

PS Мне не очень понятна сама идея вывода 10 случайных записей соответствующих паттерну. Не лучше ли выводить 10 самых релевантных? :) Или у вас слишком общий паттерн и о релевантности речи не идет? Уточните этот момент пожалуйста.
Ответ написан
t-alexashka
@t-alexashka
Сразу пишу legacy код
www.warpconduit.net/2011/03/23/selecting-a-random-...

вот тут 3 примера шустрых rand() выборок
Ответ написан
@whats
На комментарий kazmiruk
в таком случае вам подойдет подзапрос в котором вы выбираете все LIKE записи.
SELECT * FROM (SELECT * FROM table WHERE column LIKE ?) sub


Но так как mysql инвалид во всем, то далее вам необходимо применить имитацию ROW_NUMBER()

SELECT * FROM (SELECT t.*, @row_number := @row_number + 1 AS rank FROM table t,(SELECT @row_number := 0) r WHERE t.column LIKE ?) sub


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

Далее вы в основном запросе можете делать ваш запрос что был в примере с RAND(). Но вы учтите что LIKE это медленная функция поиска, а в случае с %text% она тормозит очень сильно. Опять же повторюсь, mysql это дерьмовая база для всего кроме обычного чтения по индексу и записи. В ней этого лучше не делать. Тот же postgres со своим полнотекстовым поиском сделает все на уровне сфинкса, в некоторых случаях даже быстрее.
Ответ написан
Комментировать
@ugodrus
Если количество результатов по условию достаточно велико, то соответственно вычисляется много случайных чисел для этих полей. И LIKE я лично не использую никогда - очень тупой метод.. а когда с большими объёмами работает - вообще мрак. Попробуйте переделать под REGEX и если набор выбранных данных всегда достаточно большой - лучше переделать по другому. Советую заглянуть сюда.
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы