Комментарии 22
В принципе вполне очевидное решение, с не менее очевидными минусами:
1. Необходимость введения дополнительного стобца и дополнительного индекса
2. Необходимость периодического обновления этот столбца, притом f надо постоянно увеличивать с ростом таблицы
3. Неравномерное распределение, даже если в таблице нет дырок
4. Теоретически возможна ситуация, когда LIMIT 10 вернёт меньше 10 записей
Единственное преимущество по сравнению с habrahabr.ru/blogs/mysql/54176/#comment_1444785
это то, что делается один запрос, но подозреваю что он работает медленнее чем 10 моих в сумме, т.к. мой запрос не сортирует временные таблицы в памяти.
Раз уж вы ввели дополнительный столбец, и постоянно обновляете его, то лучше в этом столбце просто хранить натуральные числа по порядку, без пропусков между ними. Тогда и распределение получим равномерное, и запрос будет выполняться с максимальной скоростью.
1. Необходимость введения дополнительного стобца и дополнительного индекса
2. Необходимость периодического обновления этот столбца, притом f надо постоянно увеличивать с ростом таблицы
3. Неравномерное распределение, даже если в таблице нет дырок
4. Теоретически возможна ситуация, когда LIMIT 10 вернёт меньше 10 записей
Единственное преимущество по сравнению с habrahabr.ru/blogs/mysql/54176/#comment_1444785
это то, что делается один запрос, но подозреваю что он работает медленнее чем 10 моих в сумме, т.к. мой запрос не сортирует временные таблицы в памяти.
Раз уж вы ввели дополнительный столбец, и постоянно обновляете его, то лучше в этом столбце просто хранить натуральные числа по порядку, без пропусков между ними. Тогда и распределение получим равномерное, и запрос будет выполняться с максимальной скоростью.
Отличная, на самом деле, идея.
Чтобы в «ручную не менять f при каждом пуске»
можно немножко модифицировать запрос:
SELECT * FROM `a` WHERE `f` = RAND()*1000 ORDER BY RAND() LIMIT 10;
Чтобы в «ручную не менять f при каждом пуске»
можно немножко модифицировать запрос:
SELECT * FROM `a` WHERE `f` = RAND()*1000 ORDER BY RAND() LIMIT 10;
Может везде
`f` = x заменить на `f` >= x, тогда по идее выборок менее чем с десятью элементами станет меньше.
Попытаюсь исправится, ибо утром был ещё сонным(можете заминусовать его, чтоб скрылся =)).
Слегка переработанный вариант предложенный TimTowdy. Учитывая, что MAX(id) мы можем закешировать, будет выполнятся очень быстро.
Из минусов такого подхода — работает только при хорошей кучности id. При проверке на таблице phpbb_privmsgs результат оказался довольно удручающим.
Из предложений: Не делать поле id auto_increment, а при добавлении приравнивать его значение к RAND*MAXINT; Тогда дисперсия будет равномерной, но появятся проблемы с добавлением при сильной заполненности таблицы.
А вообще подход автора весьма интересен.
mysql> EXPLAIN SELECT * FROM a WHERE id >= RAND()*(SELECT MAX(id) FROM a) LIMIT 1 \G;Слегка переработанный вариант предложенный TimTowdy. Учитывая, что MAX(id) мы можем закешировать, будет выполнятся очень быстро.
Из минусов такого подхода — работает только при хорошей кучности id. При проверке на таблице phpbb_privmsgs результат оказался довольно удручающим.
Из предложений: Не делать поле id auto_increment, а при добавлении приравнивать его значение к RAND*MAXINT; Тогда дисперсия будет равномерной, но появятся проблемы с добавлением при сильной заполненности таблицы.
А вообще подход автора весьма интересен.
Ещё немного подумав о подходе предложенном miami
К сожалению он добавляет некую дольку «псевдослучайности». То есть фактически, в приведённом примере, при запросе десяти записей по одному и тому же `f` у нас есть всего ~16 непересекающихся вариантов выборок, также отсутствует даже теоретическая возможность попадания в эту выборку записей с другим значением `f`
К сожалению он добавляет некую дольку «псевдослучайности». То есть фактически, в приведённом примере, при запросе десяти записей по одному и тому же `f` у нас есть всего ~16 непересекающихся вариантов выборок, также отсутствует даже теоретическая возможность попадания в эту выборку записей с другим значением `f`
Значение f — это не критерий поиска, это всего лишь ускорение прохода по таблице. Смысл не в том, чтобы выбрать из различных f ;))) Хотя да, эсли внезапно понадобилось более 10 случайных результатов, а индекс перестраивать не хочется, то подойдёт такой вариант:
SELECT * FROM `a` WHERE `f` IN (100,500,x5,x25,... ) ORDER BY RAND() LIMIT 10;Это я и имел виду под пунктом 3. Хотя если f часто обновлять то это не существенно.
Имеется 1000 (максимальный f) абсолютно непересекающихся «рандомов» которые в свою очередь перетасовываются ORDER BY RAND(), а из полученного уже берётся 10 верхних записей. Если перегенеривать значения колонки f каждые, скажем 1000 запросов… или 100… то где здесь псевдослучайность? ;) Если вам не нравится функция RAND() можете использовать посложнее:
Проверяем качество разброса:
UPDATE `a` SET `f` = HEX(MID(MD5(RAND()),3,2)); -> 163072 row(s) affected. ( Query took 4.5446 sec )Проверяем качество разброса:
SELECT `f`, COUNT(1) C FROM `a` GROUP BY `f` LIMIT 10; ->SET @rnd = HEX(MID(MD5(RAND()),3,2));
SELECT * FROM `a` WHERE `f` = @rnd ORDER BY RAND() LIMIT 10;Да, так псевдослучайность убирается.
Есть предложение использовать такой алгоритм для `f`:
Из минусов: `f` завязывается на степенях 16ти
Остался только вопрос в сравнении скорости алгоритмов предложенных Вами и TimTowdy на разных объёмах выборки(1,10,100,1000 например).
Есть предложение использовать такой алгоритм для `f`:
SELECT CONV(MID(MD5(RAND()),-2),16,10);Из минусов: `f` завязывается на степенях 16ти
Остался только вопрос в сравнении скорости алгоритмов предложенных Вами и TimTowdy на разных объёмах выборки(1,10,100,1000 например).
На всё той же своей таблице тестирую метод TimTowdy, тот же комп, те же мощности софт- и хардвара.
Теперь самое интересное — удваиваю оъём таблицы.
SELECT r1.`id`, `md5`
FROM a AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(`id`) FROM `a`)) AS `id`
) AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1; -> (1 rows total, Query took 0.0318 sec) (SELECT r1.`id`, `md5` FROM a AS r1 JOIN
(SELECT (RAND() * (SELECT MAX(`id`) FROM `a`)) AS `id` ) AS r2
WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1)
UNION (SELECT r1.`id`, `md5` FROM a AS ... и так 10 раз подряд-> (10 rows total, Query took 0.3303 sec)DELETE FROM `a` WHERE `id` > 1000 LIMIT 3000;
INSERT INTO `a` (md5) SELECT md5 FROM `a` LIMIT 3000;Теперь самое интересное — удваиваю оъём таблицы.
INSERT INTO `a` (md5) SELECT md5 FROM `a`;
UPDATE `a` SET md5 = MD5(`id`), f = RAND()*2000;SET @rnd = RAND()*2000;
SELECT * FROM `a` WHERE `f` = @rnd ORDER BY RAND() LIMIT 10; -> 0.0021 sec - 0.0023 sec - 0.0133 sec
(SELECT r1.`id`, `md5` FROM a AS r1 JOIN
(SELECT (RAND() * (SELECT MAX(`id`) FROM `a`)) AS `id` ) AS r2
WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1)
UNION (SELECT r1.`id`, `md5` FROM a AS ... -> 0.0927 sec - 0.1218 sec - 0.3964 sec. 
«хотя знаете, можно воспользоваться первыми знаками MD5(`id`) и перевести его в INT, равномернее некуда»
Откуда такая информация?
Откуда такая информация?
Хорошая идея. Лишнее подтверждение, что чаще всего быстродействие достигается за счет расхода дополнительной памяти.
ORDER BY RAND() — зло по определению, и придумывать финты ушами можно бесконечно.
Проще от него отказаться.
Готовый пример случайного offset на чистом SQL:
SET @limit := 10;
SET @r := (SELECT COUNT(*) FROM test);
SET @offset := CEILING(RAND() * @r) — @limit;
SET @sql := CONCAT('SELECT * FROM test LIMIT ', @limit, ' OFFSET ', @offset);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
Проще от него отказаться.
Готовый пример случайного offset на чистом SQL:
SET @limit := 10;
SET @r := (SELECT COUNT(*) FROM test);
SET @offset := CEILING(RAND() * @r) — @limit;
SET @sql := CONCAT('SELECT * FROM test LIMIT ', @limit, ' OFFSET ', @offset);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
Случайный офсет не помогает сократить пробег по таблице. Это объясняет
И даже это звучит как-то не внушительно:
Но за ваш вариант спасибо… )
EXPLAIN SELECT * FROM `test` LIMIT 1 OFFSET 1234И даже это звучит как-то не внушительно:
EXPLAIN SELECT `id` FROM `test` LIMIT 1 OFFSET 1234Но за ваш вариант спасибо… )
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Ускоряем выборку произвольных записей MySQL