Оптимизация ORDER BY RAND()

Не редко появляется необходимость рандомно вытащить несколько записей из базы данных, например показать 5 случайных пользователей которые сегодня заходили или 10 случайных товаров в интернет магазине, в общем я думаю суть ясна. Конечно когда у вас в базе всего 100-500 записей это не составит особой нагрузки, а вот если у вас в базе 100000-500000 записей и более, то пиши пропало. Конечно есть такие чудные штуки как memcached и им подобные, но мы попробуем как можно лучше оптимизировать сам запрос.

Как правило подобная задача решается так:

SELECT * FROM post ORDER BY RAND() LIMIT 10;

Как уже говорилось для маленького проекта это будет работать, но когда проект разрастется начнутся серьезные проблемы с подобными запросами. Кстати проблема начнет о себе напоминать когда в количество записей перевалит хотя бы за 4000-5000. На моем рабочем компьютере такой запрос выполняется за 0,32 секунды в таблице содержится 130000 записей. Воспользуемся такой утилитой как EXPLAIN, она нам показывает Using temporary; Using filesort, что означает создание временной таблицы (а для большого количества записей она ещё и на диск пишется) и не использование индекса.

Чтобы использовать индекс можно выбрать не все данные, а только id, для этого не много подредактируем запрос:

SELECT id FROM post ORDER BY RAND() LIMIT 10;

Запрос нам выдаст десять id, имея их мы всегда можем получить всю запись:

SELECT post.* FROM (
    SELECT id
    FROM post
    ORDER BY RAND()
    LIMIT 10
)
AS ids JOIN post ON post.id = ids.id

В результате получаем то что и получали из первого запроса только на много быстрее, а именно за 0.0900 секунды.

П.С. Естественно поле id имеет индекс и является автоинкрементным.