Comments 42
Проблему с дырами во втором способе можно решить заменив знак равенства на «меньше либо равно» (и не забыть добавить LIMIT).
Статистическое распределение конечно ухудшится, но выбирать можно будет очень быстро.
Особенно если вместо count подзапроса использовать передаваемую в запрос константу, примерно равную числу записей в таблице.
Статистическое распределение конечно ухудшится, но выбирать можно будет очень быстро.
Особенно если вместо count подзапроса использовать передаваемую в запрос константу, примерно равную числу записей в таблице.
+1
<= выдаст самую первую запись. Может «больше или равно»?
Или я что-то не так понял…
Или я что-то не так понял…
0
Ну это смотря какую сортировку использовать.
Допустим у нас на сайте чуть больше 100 000 комментариев, и мы хотим выбрать случайный. Мы не стремимся выводить самые свежие, там может быть спам, да и пересечение с виджетом «Последние комментарии» нежелательно. Мы можем смело передавать константу 100 000 в запрос. А ещё лучше, сразу случайное число от 1 до 100000.
тогда запрос будет
или соответственно
Допустим у нас на сайте чуть больше 100 000 комментариев, и мы хотим выбрать случайный. Мы не стремимся выводить самые свежие, там может быть спам, да и пересечение с виджетом «Последние комментарии» нежелательно. Мы можем смело передавать константу 100 000 в запрос. А ещё лучше, сразу случайное число от 1 до 100000.
тогда запрос будет
SELECT * FROM comments WHERE id <= (rand()*100000) ORDER BY id DESC LIMIT 1;
или соответственно
SELECT * FROM comments WHERE id <= 63244 ORDER BY id DESC LIMIT 1;
+1
хм… на моей таблице ваш запрос срабатывает за 3 секунды на 1 строку…
C rand() естественно.
C rand() естественно.
0
Даже не знаю, у меня на таблице в миллион за 1/1000 проходит, SQL_NO_CACHE добавил.
RAND() и умножение не может выполнятся 3 секунды.
Один вариант есть на уме — возможно из-за того что результат вычисления случайного числа дробный mysql пытается все id привести к этому же типу. Попробуйте взять вычисление во FLOOR().
RAND() и умножение не может выполнятся 3 секунды.
Один вариант есть на уме — возможно из-за того что результат вычисления случайного числа дробный mysql пытается все id привести к этому же типу. Попробуйте взять вычисление во FLOOR().
0
Не забывайте, что функция RAND() — детерминированная, и соответственно в WHERE у вас каждый раз для каждой строки будет вычисляться новое число rand()*100000, так что про индексы тут можете смело забыть.
+1
Если PK «не имеет дыр и начинается с 1» — тогда дешевле всего сгенерировать случайные значения отдельно и передать их в запрос.
Это я к тому, что один запрос — не всегда лучше, чем два (вообще непонятно, откуда такая точка зрения возникает).
Это я к тому, что один запрос — не всегда лучше, чем два (вообще непонятно, откуда такая точка зрения возникает).
+12
Еще можно юзать колонку в рандомным числом
+1
Если заранее известно количество элементов, тогда выборка по случайному offset будет намного эффективнее. Я проверял это на postgresql и mysql — разница, насколько я помню, в 5-10 раз в пользу выборки по смещению.
Хочется отметить, что при использовании rand() в SQL выражении идут лесом любые индексы и оптимизации по ним. Так что категорически не советую его использовать в выражениях WHERE или ORDER.
Хочется отметить, что при использовании rand() в SQL выражении идут лесом любые индексы и оптимизации по ним. Так что категорически не советую его использовать в выражениях WHERE или ORDER.
0
Выборка по смещению не быстрее выборки по PK. Попробуйте сдвиньтесь на 100000000 рядов, а потом выберите тот же ряд по PK.
0
Выборка по смещению и есть выборка по PK. И такая выборка быстрее чем выборка с использованием random в запросе.
0
> Выборка по смещению и есть выборка по PK
С чего вы это взяли? Смещение — это OFFSET в postgresql или N в LIMIT N, 1 в mysql. А выборка по PK это выборка по PK.
С чего вы это взяли? Смещение — это OFFSET в postgresql или N в LIMIT N, 1 в mysql. А выборка по PK это выборка по PK.
0
Если вы по третий метод, то это надо тестировать. Я не уверен что именно быстрее. Возможно вы и правы. Но, думаю, разница по времени будет не существенная.
0
Я вообще вне контекста статьи — LIMIT операция медленная. Чем дальше от начала — тем медленнее.
А выборка по PK практически константная
А выборка по PK практически константная
+1
Возможно, что это справедливо для MySQL. В postgresql я это тестировал и обнаружил что для LIMIT практически нет зависимости от величины смещения. По крайней мере если сортировка идет по PK.
0
полностью согласен — проверено на практике.
0
Если дыр нет, известно количество строк, а из базы вам нужно извлечь только pk_id, то… вам вообще не нужно делать запросы в базу. :) Либо нужно один раз запросить количество строк.
Просто генерируете в приложении позиции строк путем умножения COUNT на rand(), а так как у вас ключи соответствуют ряду натуральных чисел, то pk_id собственно и равен позиции строки.
Просто генерируете в приложении позиции строк путем умножения COUNT на rand(), а так как у вас ключи соответствуют ряду натуральных чисел, то pk_id собственно и равен позиции строки.
+5
Насколько мне известно, нет никакой необходимости залезать в information_schema, поскольку это эквивалентно простому запросу
Заменив COUNT(pk_id) на (SELECT COUNT(*) FROM test) в вашем запросе из второго варианта:
К слову, у меня такой запрос выполняется примерно на 50% быстрее вашего третьего варианта с information_schema: 0.0008 сек против 0.0012 сек на таблице в 3 млн записей. Полагаю, на таблицах существенно большего размера эта разница сойдет на нет. Проверить, к сожалению, сейчас не могу.
SELECT COUNT(*) FROM test
Этот запрос выполняется условно-мгновенно, не обращаясь к собственно данным таблицы.Заменив COUNT(pk_id) на (SELECT COUNT(*) FROM test) в вашем запросе из второго варианта:
SELECT t.pk_id FROM test as t,
(SELECT ROUND((SELECT COUNT(*) FROM test) * rand()) as rnd FROM test LIMIT 1) t
WHERE t.pk_id = rnd
я получил более чем десятикратное ускорение выполнения.К слову, у меня такой запрос выполняется примерно на 50% быстрее вашего третьего варианта с information_schema: 0.0008 сек против 0.0012 сек на таблице в 3 млн записей. Полагаю, на таблицах существенно большего размера эта разница сойдет на нет. Проверить, к сожалению, сейчас не могу.
+3
> Этот запрос выполняется условно-мгновенно, не обращаясь к собственно данным таблицы.
Только если у вас myisam
Только если у вас myisam
+3
Второй вариант с COUNT(*) прошел за 0.95 против 1.04… быстрее!
Просто проблема в том, что с COUNT даже 10 случайных строк получить будет довольно таки сложно.
Просто проблема в том, что с COUNT даже 10 случайных строк получить будет довольно таки сложно.
0
Надо просто добиться того, чтобы count считался один лишь раз
сорри не спец в mySql, думаю это будет выглядить как-то так
сорри не спец в mySql, думаю это будет выглядить как-то так
select * from table
where id in (
select trunc(rand()*cnt.cnt+1)
from (select count(*) as cnt from table) as cnt
cross join (select null from table limit :demanded_ranom_records_count) as pivot
)
0
впрочем, этот способ мало отличается от приведенного вами max+rand. Если вы в ваш max+rand поставите вместо max, count, по идее, получите похожий результат
0
Если взять COUNT(*) вместо MAX(pk_id), то делает в 1000 раз медленнее…
не знаю даже, почему)
Вообще с COUNT не удалось получать результат менее чем за секунду.
P.s таблица InnoDB
не знаю даже, почему)
Вообще с COUNT не удалось получать результат менее чем за секунду.
P.s таблица InnoDB
0
>>не знаю даже, почему)
хм… тоже не знаю… возможно при max(pk_id) просто берется с хвоста индекса. Т.е. де факто отрабатывает запрос select id from table order by id desc limit 1. Было бы интересно, если бы это было действительно так.
хм… тоже не знаю… возможно при max(pk_id) просто берется с хвоста индекса. Т.е. де факто отрабатывает запрос select id from table order by id desc limit 1. Было бы интересно, если бы это было действительно так.
0
На information_schema.tables я бы на вашем месте опираться не стал, т.к., например, для InnoDB столбец TABLE_ROWS содержит лишь грубую оценку количества строк в таблице, а не точное значение.
0
А почему бы не сделать вместо
вот так
?
(SELECT table_rows as tr FROM information_schema.tables WHERE table_name = 'test')
вот так
(SELECT tt.pk_id FROM test AS tt ORDER BY tt.pk_id DESC LIMIT 1)
?
0
Если InnoDB — то да. А если MyISAM — то SELECT COUNT(*).
0
Хм, для одной строки прирост почти в 10 раз с вашим вариантом подзапроса.
Но в таком случае можно было бы использовать MAX а не ORDER BY + LIMIT.
Хотя особой разницы не вижу, что-то, что то делается за 0.000 — 0.001 секунды…
Но в таком случае можно было бы использовать MAX а не ORDER BY + LIMIT.
Хотя особой разницы не вижу, что-то, что то делается за 0.000 — 0.001 секунды…
0
разница есть.
А запрос к I_S… там две проблемы. Во-первых, нет
MAX
или ORDER BY + LIMIT
— практически все равно.А запрос к I_S… там две проблемы. Во-первых, нет
TABLE_SCHEMA="foobar"
; в WHERE
. Это значит, что при запросе сервер построит список всех каталогов в datadir-е, и в каждом будет пытаться найти файл test.frm. У хостеров этих каталогов могут быть тысячи. Во-вторых, table_rows
из I_S.TABLES
— это быстрая, но приблизительная оценка количества строк в таблице. Точная она в MyISAM (где COUNT(*)
и так быстрый), но не в InnoDB (где точный результат — COUNT(*)
— весьма медленный).0
Большое спасибо! Очень полезно. Как раз сейчас этим занимаюсь
0
А что если нужны дополнительные ограничения на поля? Где ограничивать?
0
Sign up to leave a comment.
MySQL. Выбор случайных строк в один запрос