MySQL: альтернативы ORDER BY RAND()

Я прочитал несколько альтернатив функции MySQL ORDER BY RAND(), но большинство альтернатив применимо только к тому, где требуется один случайный результат.

Кто-нибудь знает, как оптимизировать запрос, который возвращает несколько случайных результатов, например:

   SELECT u.id, 
          p.photo 
     FROM users u, profiles p 
    WHERE p.memberid = u.id 
      AND p.photo != '' 
      AND (u.ownership=1 OR u.stamp=1) 
 ORDER BY RAND() 
    LIMIT 18

Ответ 1

ОБНОВЛЕНИЕ 2016

Это решение работает лучше всего с помощью индексированного столбца.

Вот простой пример и оптимизированный столбец запросов, отмеченный 100 000 строк.

ОПТИМИЗАЦИЯ: 300 мс

SELECT 
    g.*
FROM
    table g
        JOIN
    (SELECT 
        id
    FROM
        table
    WHERE
        RAND() < (SELECT 
                ((4 / COUNT(*)) * 10)
            FROM
                table)
    ORDER BY RAND()
    LIMIT 4) AS z ON z.id= g.id

примечание о предельном размере: ограничение 4 и 4/count (*). 4s должны быть одинаковыми. Изменение того, сколько вы вернетесь, не так сильно влияет на скорость. Контрольный показатель в пределе 4 и предел 1000 совпадают. Предел 10000 взял его до 600 мс

note about join: рандомизация только идентификатора быстрее, чем рандомизация целой строки. Так как он должен скопировать всю строку в память, а затем рандомизировать ее. Соединение может представлять собой любую таблицу, связанную с подзапросом "С", чтобы предотвратить использование таблиц.

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

примечание sub query: если вы выполняете соединения и дополнительные условия условия предложения, вам нужно поместить их как в подзапрос, так и в подзапрос. Для точного подсчета и возврата правильных данных.

UNOPTIMIZED: 1200 мс

SELECT 
    g.*
FROM
    table g
ORDER BY RAND()
LIMIT 4

PROS

4 раза быстрее, чем order by rand(). Это решение может работать с любой таблицей с индексированным столбцом.

CONS

Это сложный процесс со сложными запросами. Необходимо поддерживать 2 базы кода в подзапросах

Ответ 2

Здесь альтернатива, но она по-прежнему основана на использовании RAND():

  SELECT u.id, 
         p.photo,
         ROUND(RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

Это немного сложнее, но дает лучшее распределение значений random_ind:

  SELECT u.id, 
         p.photo,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) - 1 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

Ответ 3

Это не самый быстрый, но более быстрый, чем обычный способ ORDER BY RAND():

ORDER BY RAND() не так медленно, когда вы используете его, чтобы найти только индексированный столбец. Вы можете взять все свои идентификаторы в одном запросе следующим образом:

SELECT id
FROM testTable
ORDER BY RAND();

чтобы получить последовательность случайных идентификаторов, а JOIN результат к другому запросу с другими параметрами SELECT или WHERE:

SELECT t.*
FROM testTable 
JOIN
    (SELECT id
    FROM `testTable`
    ORDER BY RAND()) AS z ON z.id= t.id   
WHERE isVisible = 1
LIMIT 100;

в вашем случае это будет:

SELECT u.id, p.photo 
FROM users u, profiles p 
JOIN
    (SELECT id
    FROM users
    ORDER BY RAND()) AS z ON z.id= u.id   
WHERE p.memberid = u.id 
  AND p.photo != '' 
  AND (u.ownership=1 OR u.stamp=1) 
LIMIT 18

Это очень тупой метод, и он может быть неправильным с очень большими таблицами, но все же он быстрее обычного RAND(). Я получил в 20 раз быстрее время выполнения поиска 3000 случайных строк почти в 400 000.

Ответ 4

Создайте столбец или присоединитесь к выбору со случайными номерами (сгенерированными, например, php) и упорядочитесь по этому столбцу.

Ответ 5

Я столкнулся с этим сегодня и пытался использовать "DISTINCT" вместе с JOINs, но получал дубликаты, которые я предполагаю, потому что RAND делал каждую строку JOINed отличной. Я немного запутался и нашел решение, которое работает, например:

SELECT DISTINCT t.id, 
                t.photo 
       FROM (SELECT  u.id, 
                     p.photo,
                     RAND() as rand
                FROM users u, profiles p 
                 WHERE p.memberid = u.id 
                  AND p.photo != '' 
                  AND (u.ownership=1 OR u.stamp=1)
                ORDER BY rand) t
       LIMIT 18

Ответ 6

Решение, которое я использую, также размещено по следующей ссылке: Как я могу оптимизировать функцию ORDER BY RAND() MySQL?

Я предполагаю, что таблица ваших пользователей будет больше, чем ваша таблица профилей, если нет, то от 1 до 1 мощности.

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

Сначала сделайте выделение:

SELECT *
FROM users
WHERE users.ownership = 1 OR users.stamp = 1

Затем из этого пула выберем случайные строки по расчетной вероятности. Если ваша таблица имеет M строк и вы хотите выбрать N случайных строк, вероятность случайного выбора должна быть N/M. Следовательно:

SELECT *
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

Где N - $limitCount, а M - подзапрос, который вычисляет счетчик строк таблицы. Однако, поскольку мы работаем над вероятностью, возможно получить МЕНЬШЕ, чем $limitCount возвращаемых строк. Поэтому мы должны умножить N на коэффициент увеличения случайного пула.

то есть:

SELECT*
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

Обычно я устанавливаю значение $factor = 2. Вы можете установить коэффициент на более низкое значение, чтобы дополнительно уменьшить размер случайного пула (например, 1.5).

В этот момент мы бы уже ограничили таблицу размеров M примерно до 2N. Отсюда мы можем сделать JOIN, затем LIMIT.

SELECT * 
FROM
(
       SELECT *
        FROM
        (
            SELECT *
            FROM users
            WHERE users.ownership = 1 OR users.stamp = 1
        ) as U
        WHERE 
            rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)
) as randUser
JOIN profiles
ON randUser.id = profiles.memberid AND profiles.photo != ''
LIMIT $limitCount

В большой таблице этот запрос будет превосходить обычный запрос ORDER по запросу RAND().

Надеюсь, это поможет!

Ответ 7

Order by rand() очень медленный на больших таблицах,

Я нашел следующее обходное решение в php script:

Select min(id) as min, max(id) as max from table;

Тогда сделайте случайное в php

$rand = rand($min, $max);

Тогда

'Select * from table where id>'.$rand.' limit 1';

Кажется, довольно быстро....