MySQL оптимизация: ORDER BY RAND()

Лирическое вступление:

Довольно часто у нас возникает потребность выборки случайных данных из mysql базы данных. Как правило времени нет и используется самая простая конструкция вида SELECT [что-то] FROM [где-то] WHERE [то и сё] ORDER BY RAND(). Эта конструкция работает на ура. Но вот прототип выезжает на продуктовые сервера и такой милый сердцу запрос вдруг начинает выпадать в топы медленных логов. Ниже будут рассмотрены несколько возможностей для оптимизации этого запроса по нарастанию их эффективности:

В первых примерах мы полагаем что ID стартует с номера 1 и в ID нет разрывов между 1 и максимальным ID.

#1. Передать всю работу в приложение

Мы можем тупо слить всю работу по определению случайного номера в приложение.

SELECT MAX(id) FROM random;
## генерируем случайный ID в приложении
SELECT name FROM random WHERE id = id_из_приложения

Так как MAX(id) == COUNT(id) нам всеголишь нужно сгенерировать случайное число между 1 и MAX(id), передать его в запрос к БД и получить свою случайную строку.

Первый SELECT у нас фактически NO-OP и он оптимизирован по самое “не балуйся”. Второй запрос это eq_ref по константе и он тоже очень быстр.

#2. Делаем всю работу на стороне базы данных

Но настолько ли необходимо делать случайные выборки через приложение? Может стоит вынести “грязную” работу на сторону базы данных (прим. пер.: на самом деле первый способ это практически сферический конь в вакууме – большинство реальных задач будет выходить за рамки его применимости)

# генерируем случайный ID
> SELECT RAND() * MAX(id) FROM random;
+------------------+
| RAND() * MAX(id) |
+------------------+
|  689.37582507297 |
+------------------+

упс, это число типа double, а нам нужен int

> SELECT CEIL(RAND() * MAX(id)) FROM random;
+-------------------------+
| CEIL(RAND() * MAX(id))  |
+-------------------------+
|                1000000  |
+-------------------------+

уже лучше, но что насчет скорости?

> EXPLAIN
SELECT CEIL(RAND() * MAX(id)) FROM random;
+----+-------------+-------+-------+---------+-------------+
| id | select_type | table | type  |   rows  | Extra       |
+----+-------------+-------+-------+---------+-------------+
|  1 | SIMPLE      |random | index | 1000000 | Using index |
+----+-------------+-------+-------+---------+-------------+

index scan? похоже мы потеряли оптимизацию MAX()

> EXPLAIN
SELECT CEIL(RAND() * (SELECT MAX(id) FROM random));
+----+-------------+-------+------+------+------------------------------+
| id | select_type | table | type | rows | Extra                        |
+----+-------------+-------+------+------+------------------------------+
|  1 | PRIMARY     | NULL  | NULL | NULL | No tables used               |
|  2 | SUBQUERY    | NULL  | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+------+------------------------------+

Ура! Простой подзапрос возвращает нашу потерянную производительность!

Окей, теперь мы знаем как сгенерировать случайный ID, теперь надо получить и соответствующую ему строку:

> EXPLAIN
SELECT name
FROM random
WHERE id = (SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random));
+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra                        |
+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+
|  1 | PRIMARY     | random | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where                  |
|  3 | SUBQUERY    | NULL   | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away |
+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+
> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1249 | Select 2 was reduced during optimization |
+-------+------+------------------------------------------+

Нет нет нет! Не идите этим путем! Это самый очевидный, но также самый неверный способ! Почему? А вот почему: SELECT в условии WHERE будет выполняться для каждой строки! А это число может составлять от 0 до 4091 строки, в зависимости от того насколько вы будете удачливы.

Нам нужен такой способ выборки, при котором мы будем уверены что случайный номер генерируется только однажды:

SELECT name
FROM random
JOIN
(
  SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)) AS id
) AS r2
USING (id);
+----+-------------+------------+--------+------+------------------------------+
| id | select_type | table      | type   | rows | Extra                        |
+----+-------------+------------+--------+------+------------------------------+
|  1 | PRIMARY     |            | system |    1 |                              |
|  1 | PRIMARY     | random     | const  |    1 |                              |
|  2 | DERIVED     | NULL       | NULL   | NULL | No tables used               |
|  3 | SUBQUERY    | NULL       | NULL   | NULL | Select tables optimized away |
+----+-------------+------------+--------+------+------------------------------+

Внутренний SELECT генерирует константу в TEMPORARY таблицу и JOIN выбирает одну строку. Великолепно! Нет сортировок, нет вмешательства приложения. Все части запроса оптимизированы.

#3. Добавляем “дыры” в primary key

Для того чтобы сделать наше предыдущее решение более универсальным, нам нужно учесть возможность “дыр” в ID (как если бы вы удалили некоторые строки).

SELECT name
FROM random AS r1 JOIN
(
 SELECT (RAND() * (SELECT MAX(id) FROM random)) AS id
)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;
+----+-------------+------------+--------+------+------------------------------+
| id | select_type | table      | type   | rows | Extra                        |
+----+-------------+------------+--------+------+------------------------------+
|  1 | PRIMARY     |            | system |    1 |                              |
|  1 | PRIMARY     | r1         | range  |  689 | Using where                  |
|  2 | DERIVED     | NULL       | NULL   | NULL | No tables used               |
|  3 | SUBQUERY    | NULL       | NULL   | NULL | Select tables optimized away |
+----+-------------+------------+--------+------+------------------------------+

Теперь JOIN добавляет все ID который больше или равны нашему случайному значению и мы выбираем ближайшего соседа, если равенство не возможно. НО как только одна строка найдена мы останавливаемся (LIMIT 1). И мы читаем строки в соответствии с индексом (ORDER BY id ASC). Так как мы используем знак “>=” вместо строгого равенства “=” мы можем избавиться от CEIL и получить тот же резудьтат при немного меньших затратах.

#4. Равномерное распределение

Поскольку распределение ID не равномерно, наша выборка на самом деле стала не совсем случайной (прим. пер.: насколько я понимаю, чем больше “больших” дыр в ID тем менее равномерно распределение и тем “менее случайной” будет выборка).

> select * from holes;
+----+----------------------------------+----------+
| id | name                             | accesses |
+----+----------------------------------+----------+
|  1 | d12b2551c6cb7d7a64e40221569a8571 |      107 |
|  2 | f82ad6f29c9a680d7873d1bef822e3e9 |       50 |
|  4 | 9da1ed7dbbdcc6ec90d6cb139521f14a |      132 |
|  8 | 677a196206d93cdf18c3744905b94f73 |      230 |
| 16 | b7556d8ed40587a33dc5c449ae0345aa |      481 |
+----+----------------------------------+----------+

Функция RAND генерирует ID от 9 до 15, которые попадают в “дыру” перед 16 и как следствие, 16 выбирается намного чаще чем остальные.

Для этой проблемы не существует нормального решения, но если ваши данные более-менее постоянны, вы можете добавить таблицу для маппинга номера строки с ее ID:

> create table holes_map (
>   row_id int not NULL primary key,
>   random_id int not null
> );
> SET @id = 0;
> INSERT INTO holes_map SELECT @id := @id + 1, id FROM holes;
> select * from holes_map;
+--------+-----------+
| row_id | random_id |
+--------+-----------+
|      1 |         1 |
|      2 |         2 |
|      3 |         4 |
|      4 |         8 |
|      5 |        16 |
+--------+-----------+

Идентификатор row_id теперь не содержит дыр и мы опять можем воспользоваться нашим запросом:

SELECT name
FROM holes
JOIN (
  SELECT r1.random_id
  FROM holes_map AS r1
  JOIN (
    SELECT (RAND() * (SELECT MAX(row_id) FROM holes_map)
  ) AS row_id
) AS r2
WHERE r1.row_id >= r2.row_id
ORDER BY r1.row_id ASC
LIMIT 1) as rows ON (id = random_id);

После 1000 попыток опять имеем равномерное распределение:

> select * from holes;
+----+----------------------------------+----------+
| id | name                             | accesses |
+----+----------------------------------+----------+
|  1 | d12b2551c6cb7d7a64e40221569a8571 |      222 |
|  2 | f82ad6f29c9a680d7873d1bef822e3e9 |      187 |
|  4 | 9da1ed7dbbdcc6ec90d6cb139521f14a |      195 |
|  8 | 677a196206d93cdf18c3744905b94f73 |      207 |
| 16 | b7556d8ed40587a33dc5c449ae0345aa |      189 |
+----+----------------------------------+----------+

#5. Обслуживание таблицы Holes при помощи триггеров

Давайте подготовим таблицы как описано ниже:

DROP TABLE IF EXISTS r2;
CREATE TABLE r2 (
  id SERIAL,
  name VARCHAR(32) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS r2_equi_dist;
CREATE TABLE r2_equi_dist (
  id SERIAL,
  r2_id bigint unsigned NOT NULL UNIQUE
);

Когда мы что-то меняем в r2, мы хотим чтобы r2_equi_dist также изменялась.

DELIMITER $$
DROP TRIGGER IF EXISTS tai_r2$$
CREATE TRIGGER tai_r2
AFTER INSERT ON r2 FOR EACH ROW
BEGIN
DECLARE m BIGINT UNSIGNED DEFAULT 1;

SELECT MAX(id) + 1 FROM r2_equi_dist INTO m;
SELECT IFNULL(m, 1) INTO m;
INSERT INTO r2_equi_dist (id, r2_id) VALUES (m, NEW.id);
END$$
DELIMITER ;

DELETE FROM r2;

INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );
INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );
INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );
INSERT INTO r2 VALUES ( NULL, MD5(RAND()) );
SELECT * FROM r2;
+----+----------------------------------+
| id | name                             |
+----+----------------------------------+
|  1 | 8b4cf277a3343cdefbe19aa4dabc40e1 |
|  2 | a09a3959d68187ce48f4fe7e388926a9 |
|  3 | 4e1897cd6d326f8079108292376fa7d5 |
|  4 | 29a5e3ed838db497aa330878920ec01b |
+----+----------------------------------+
SELECT * FROM r2_equi_dist;
+----+-------+
| id | r2_id |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
+----+-------+

INSERT весьма прост. При DELETE же мы хотим поддерживать equi-dist-id в состоянии “без дыр”:

DELIMITER $$
DROP TRIGGER IF EXISTS tad_r2$$
CREATE TRIGGER tad_r2
AFTER DELETE ON r2 FOR EACH ROW
BEGIN
DELETE FROM r2_equi_dist WHERE r2_id = OLD.id;
UPDATE r2_equi_dist SET id = id - 1 WHERE r2_id > OLD.id;
END$$
DELIMITER ;
DELETE FROM r2 WHERE id = 2;

SELECT * FROM r2;
+----+----------------------------------+
| id | name                             |
+----+----------------------------------+
|  1 | 8b4cf277a3343cdefbe19aa4dabc40e1 |
|  3 | 4e1897cd6d326f8079108292376fa7d5 |
|  4 | 29a5e3ed838db497aa330878920ec01b |
+----+----------------------------------+
SELECT * FROM r2_equi_dist;
+----+-------+
| id | r2_id |
+----+-------+
|  1 |     1 |
|  2 |     3 |
|  3 |     4 |
+----+-------+

UPDATE также прост. Мы должны обслужить лишь Foreign Key constraint:

DELIMITER $$
DROP TRIGGER IF EXISTS tau_r2$$
CREATE TRIGGER tau_r2
AFTER UPDATE ON r2 FOR EACH ROW
BEGIN
UPDATE r2_equi_dist SET r2_id = NEW.id WHERE r2_id = OLD.id;
END$$
DELIMITER ;
UPDATE r2 SET id = 25 WHERE id = 4;

SELECT * FROM r2;
+----+----------------------------------+
| id | name                             |
+----+----------------------------------+
|  1 | 8b4cf277a3343cdefbe19aa4dabc40e1 |
|  3 | 4e1897cd6d326f8079108292376fa7d5 |
| 25 | 29a5e3ed838db497aa330878920ec01b |
+----+----------------------------------+
SELECT * FROM r2_equi_dist;
+----+-------+
| id | r2_id |
+----+-------+
|  1 |     1 |
|  2 |     3 |
|  3 |    25 |
+----+-------+

#6. Несколько случайных строк за один раз

Если вы хотите получить более одной случайной строки за раз вы можете:

  1. Выполнить запрос несколько раз
  2. Написать хранимую процедуру, которая выполняет запрос и хранит результат во временной таблице
  3. Выполнить UNION наконец

Хранимая процедура:

Хранимая процедура позволяет вам использовать структуры, известные в любом популярном языке программирования:

  1. Циклы
  2. Управляющие конструкции
  3. Процедуры

Для нашей задачи нам нужен только цикл LOOP:

DELIMITER $$
DROP PROCEDURE IF EXISTS get_rands$$
CREATE PROCEDURE get_rands(IN cnt INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id INT );

loop_me: LOOP
IF cnt < 1
  THEN LEAVE loop_me;
END IF;
INSERT INTO rands
  SELECT r1.id
  FROM random AS r1
  JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM random)) AS id
  ) AS r2
  WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;

SET cnt = cnt - 1;
END LOOP loop_me;
END$$
DELIMITER ;
CALL get_rands(4);
SELECT * FROM rands;
+---------+
| rand_id |
+---------+
|  133716 |
|  702643 |
|  112066 |
|  452400 |
+---------+

Оставляю в качестве заданий читателю следующие задачки:

  1. Динамически составлять запрос, генерируя название временной таблицы (спасибо 2 Evgeny Babin)
  2. Используя UNIQUE index отлавливать нарушения UNIQUE key для удаления возможных дублей.

#7. Быстродействие

Чтоже стало с быстродействием? У нас есть 3 различные запроса, решающие нашу проблему:

  1. Q1. ORDER BY RAND()
  2. Q2. RAND() * MAX(ID)
  3. Q3. RAND() * MAX(ID) + ORDER BY ID

Q1 можно оценить как N * log2(N), Q2 и Q3 что-то около константы.

Чтобы получить реальные значения мы провели несколько тестов с числом строк от 100 до миллиона и выполнили каждый запрос 1000 раз.

    100        1.000      10.000     100.000    1.000.000
Q1  0:00.718s  0:02.092s  0:18.684s  2:59.081s  58:20.000s
Q2  0:00.519s  0:00.607s  0:00.614s  0:00.628s   0:00.637s
Q3  0:00.570s  0:00.607s  0:00.614s  0:00.628s   0:00.637s

Как вы можете видеть, простой ORDER BY RAND() оптимизирован для выполнения при количестве строк не более 100.

По мотивам http://jan.kneschke.de/projects/mysql/order-by-rand/

Coding with fun!

P.S. Сам я на практике использовал пока лишь 3е решение ))

This entry was posted in Профессиональное and tagged , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

34 Comments

  1. sadotmd
    Posted 2010/09/16 at 4:37 pm | Permalink

    Весьма полезная информация. Спасибо!

    • hudson
      Posted 2010/09/16 at 4:41 pm | Permalink

      Спасибо! На мой взгляд про рандомные выборки – это лучшая статья. Вообще в блоге “того парня” много интересного. Надо бы почитать, да все руки не того ((

  2. lamka
    Posted 2011/02/07 at 3:05 am | Permalink

    а еще можно так:
    1) получаем COUNT(*) и генерим на стороне клиента нужное кол-во случ. величин в интервале 1 – COUNT(*)
    2) делаем еще два запроса:
    SET @r=0;
    SELECT * FROM (SELECT @r:=@r+1 AS r FROM tbl) AS t HAVING r IN (45,1200,900,2,1150);

    • hudson
      Posted 2011/02/09 at 7:31 pm | Permalink

      Об этом было написано в #1 ) Остальной пост не об этом.

  3. lamka
    Posted 2011/02/10 at 3:51 am | Permalink

    Ну если речь идет о запросе только одной рандомной строки, то вся эта статья нафиг не нужна. А вот вариант со множеством строк – совсем другое дело.

    • hudson
      Posted 2011/02/10 at 12:08 pm | Permalink

      А таблицы с разрывами? У меня в практике в основном такие. Приложение ткнет из 100 проб раз 50 пальцем в небо (ну в зависимости от разреженности таблицы конечно). Как вы с этим будете бороться?

  4. lamka
    Posted 2011/02/11 at 3:09 am | Permalink

    Вы бы хоть код посмотрели мой… Там сначала делается выборка id и номера строки (через переменную @r). И потом рандомно выбирается уже не id, а номер строки (@r). Поэтому плевать на разрывы

    • hudson
      Posted 2011/02/11 at 6:57 pm | Permalink

      Что-то я в пятницу к вечеру совсем не въезжаю в тему ))

      > получаем COUNT(*) и генерим на стороне клиента нужное кол-во случ.
      > величин в интервале 1 – COUNT(*)

      В примере эти ID – 45,1200,900,2,1150 во втором запросе? За счет чего тогда избегаются пробелы? Не могу понять. В принципе если не сложно, можем побеседовать по skype (dmitry.bykadorov)

  5. lamka
    Posted 2011/02/12 at 12:12 am | Permalink

    Пробелы избегаются потому что выборка делается не по ID, а по номеру строки, который вычисляется конструкцией @r:=@r+1. В номерах строк пробелов быть не может. Еще раз: 45,1200,900,2,1150 – не ID, а номера строк.
    😉

    • hudson
      Posted 2011/02/12 at 4:38 pm | Permalink

      Выглядит подозрительно просто )) Попробую протестировать у себя.

  6. Hett
    Posted 2011/04/05 at 2:15 am | Permalink

    lamka, а теперь попробуйте свой запрос на таблице скажем так в 5кк записей.

  7. Алексей
    Posted 2011/08/06 at 7:48 pm | Permalink

    SELECT `ci`.* FROM `catalog_item` AS `ci` INNER JOIN (SELECT `catalog_item`.`id`, RAND() AS `rnd` FROM `catalog_item` WHERE (active = “1”) ORDER BY `rnd` ASC LIMIT 100) AS `x` ON x.id = ci.id LIMIT 100

    //за 0.06 из таблицы на 20к записей

    • Posted 2011/08/11 at 6:50 pm | Permalink

      Что-то чует мое сердце есть тут подвох. Надо проанализировать запрос.

      • Дмитрий
        Posted 2011/08/31 at 6:10 pm | Permalink

        И не зря, ведь каких-то 20к для mysql это пустяк, на 400к такое чудо будет отрабатывать минимум 1,5 секунды.

        • Posted 2011/08/31 at 7:04 pm | Permalink

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

          • Дмитрий
            Posted 2011/08/31 at 11:02 pm | Permalink

            Мне пока повезло и не приходилось особо мучиться по этому поводу, сегодня в моём случае было удобнее создать доп. колонку

            ALTER TABLE `table` ADD `rid` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST ,
            ADD INDEX ( `rid` );

            Работа с таким полем оказалась почти в 250 раз быстрее(сравнивал с SQL_NO_CACHE).
            В моём случае изначально id с большими дырами, поэтому решил отказаться от удаления строк, а в дальнейшем реализовать удаление с перестройкой rid.
            Статьи хороши, теперь в закладках.

            PS: поставьте пожалуйста плагин для уведомления о комментариях по почте

  8. Alex
    Posted 2012/01/11 at 11:31 am | Permalink

    Полезная статья, помогла справиться с задачей.
    Спасибо! 🙂

  9. Ivan
    Posted 2012/01/26 at 9:37 am | Permalink

    Что-то не очень ясна логика. В конечном итоге вы, с помощью предложенного запроса, выберите номера строк. А что есть способ получить сами строки по их номерам? Предположим в целевой таблице нет такого поля как “номер_строки”, как быть?
    Ещё раз: вы получаете какие-то абстрактные номера, которые НИКАК не связаны с реальными строками целевой таблицы.

    • Posted 2012/01/26 at 9:35 pm | Permalink

      Какой именно пример вызывает такое замечание? Давайте разбирать. Заранее могу сказать что никакие они не абстрактные.

  10. Катя
    Posted 2012/03/09 at 8:53 pm | Permalink

    Пробую способ выборки из выборки и уже её сортировки, но чего-то виснет у меня MySQL.
    SELECT * FROM (SELECT DISTINCT `value` FROM `sometable`) ORDER BY RAND() LIMIT 1

    • Posted 2012/03/11 at 11:01 am | Permalink

      Если таблица большая, то неудивительно. В этой статье как раз и описано как избавиться от “ORDER BY RAND()”, который тормозит уже на 100 записях.

  11. Dendi
    Posted 2012/09/02 at 2:55 pm | Permalink

    Интересная статья, более всего понравилось то, что ORDER BY RAND() вытягивает из моей таблицы 200 записей за 0.034134 сек.(MyISAM, 230 строк, 20 полей.) Но раз все говорят, что O.B.R() работает медленное я прислушаюсь к мнению большинства.
    У меня возникла проблема выборки СТРОГО 10-ти случайных записей из таблицы по id (без разрывов), БЕЗ ПОВТОРОВ. Уже спрашивал на форумах, мне никто так и не смог помочь.

    Самый шустрый:
    $result = mysql_query(“SELECT MAX(id) FROM table”);
    $row = mysql_fetch_row($result);
    $total = $row[0];
    $int = rand(1, $total);
    $int2 = rand(1, $total); и т.д. (Но при таком раскладе попадаются записи с тем же id );

    Проще говоря у меня сейчас есть 5 вариантов выборки записей с проверкой на уникальность id, но с такими проверками нельзя рассчитывать на точное количество записей. Повторюсь: существует ли какой нибудь вариант выборки строго 10-ти записей из таблицы без повторов.

    PS. Я бы использовал O.B.R.(), но везде кричать что так нельзя.

    • Denis
      Posted 2012/10/12 at 5:44 am | Permalink

      Вот накидал примерно, как можно сгенерировать 10 значений, которые не будут повторяться:

      $ids=array();
      while(count($ids)<10){
      $i=rand(1, $total);
      $ids[$i]=$i;
      }

      // формирование запроса в базу
      "SELECT * FROM table WHERE id IN (". implode(',', $ids). ")";

      • Posted 2012/10/12 at 9:29 am | Permalink

        Ну, батенька, это не спортивно ) Кроме того не будет работать, если в таблице есть “дыры” в primary key. Если бы всё было так просто, не было бы танцев с бубнами, описанных в этой статье )))

      • Denis
        Posted 2012/10/15 at 1:24 am | Permalink

        Добрый день.
        Я отвечал вот на этот вопрос “У меня возникла проблема выборки СТРОГО 10-ти случайных записей из таблицы по id (без разрывов), БЕЗ ПОВТОРОВ. Уже спрашивал на форумах, мне никто так и не смог помочь.”

        Конечно, я понимаю всю проблему с разрывами, но так или иначе сгенерировать 10 случайных чисел, которые не повторяются, тоже пригодится для той же выборки описанные в этой статьи. Нужно поменять только то, что генерируем не 10 идентификаторов, а 10 номеров последовательностей из выборки, так сказать индексы. Я использую эту последовательность таким образом:

        SELECT * FROM table AS t
        LEFT JOIN table_rand AS r ON r.id=t.id
        WHERE r.index IN (‘.implode(‘,’, $indexs).’)

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

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

        вместо:
        while(count($ids)<10){

        нужно:
        $count = min(10,$total);
        while(count($ids)<$count){

        это нужно во избежание зацикливания при записей меньше 10.

      • Denis
        Posted 2012/10/15 at 1:31 am | Permalink

        Переписываю то что скушал скрипт комментариев:

        Где (table) – таблица, из которой вытаскиваем случайные элементы,
        (table_rand) – таблица, где хранятся идентификаторы и индексы, генерация этой таблицы уже описана в этой статьи,
        (r.id) – идентификатор таблицы (table_rand), который в свою очередь взят из таблицы (table),
        (r.index) – индекс, являются последовательной нумерацией идентификаторов,
        ($indexs) – массив случайных индексов, в диапазоне от 1 до включая максимальный индекс.

  12. Posted 2013/01/12 at 12:15 am | Permalink

    Сама толкова стаття по оптимізації!

    • Posted 2013/01/12 at 6:38 pm | Permalink

      Я всего-лишь переводчик ) Но когда мне надо делать рандомную выборку – всегда перечитываю эту статью.

  13. alex
    Posted 2013/04/06 at 5:10 pm | Permalink

    Не плохая статья, но это все замечательно, когда в проекте вы работаете с чистыми sql запросами, а что если проект работе с бд через ORM?

    • Posted 2013/04/09 at 12:01 am | Permalink

      Тут есть 2 варианта на мой взгляд:

      – либо писать хелперы на уровне ORM (как вариант – хранимки в терминах используемых СУБД)
      – либо пользоваться нативными запросами (да, частично преимущества ORM нивелируются)

  14. Posted 2015/02/01 at 1:51 pm | Permalink

    Толковая статья. Использовал ее, когда делал несколько последних проектов. Все написано понятнее некуда. В общем, зачет.

  15. Posted 2015/09/26 at 1:44 am | Permalink

    Долго я это читал и могу сказать одно, что проще два запроса делать, первый запрос MAX(), потом в массив каждому значению рандом php MAX() делим на части в зависимости сколько нам надо чисел
    array( rand( 1, 2*max/3 ), rand(max/3, 2*max/3 ), rand(2*max/3, 3*max/3 ));
    и потом этот масив, по желанию его можно в array_shuflle сделать и в запрос WHERE id IN(‘”.implode(“‘,'”,array).”‘)

  16. Александр
    Posted 2016/01/22 at 12:14 pm | Permalink

    Подскажите пожалуйста, какой можно составить запрос для быстрой выборки по критериям (с индексами) нескольких случайных записей. Только такой запрос, чтобы его можно было через php делать, без
    DELIMITER $$ DROP PROCEDURE IF EXISTS get_rands$$…. и тд., т.к. это не работает.
    Использую SQLite.

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

    Еще советовали:
    SELECT id FROM comments ORDER BY RAND() LIMIT 0,3
    а потом второй запрос для выбора текста – иначе вы будете сортировать большую таблицу текстов.
    Так быстрее идет выборка по сравнению с вариантом от Алексея.

    Пробовал делать запрос Алексея (который за 0.06 из таблицы на 20к записей) на 300к записей – за 0.486 сек 100 случайных записей.

    • Posted 2016/04/13 at 9:02 pm | Permalink

      В SQLite свои приёмы. Оптимальная выборка случайных значений – в каждой RDBMS своя…

2 Trackbacks

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*

*