SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 20.03.2009 06:42:21

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

ORDER BY RAND в большой таблице

Непомню, где но уже на форуме об этом писали. Что если идет выборка без условия и ORDER BY RAND() то дешевле сделать N запросов по соотв. полю первичного ключа. У меня есть таблица (благо пока не большая) 10 тысяч записей, делаю запрос:
SELECT `pole1`,`pole2`,`pole3`,`pole4` FROM `example_table` ORDER BY RAND() LIMIT 0,5
Довольно типичная ситуация, например выбрать несколько случайных фото из галерею на главную страницу.
Возвращаясь в N запросам просто по ID есть момент того что есть "провалы в таблицах" ибо никто не запрещает удалить фотографию. И скажем взяв количество записей в таблице посчитать средствами php 5 случайных чисел и сделать запрос или с IN или 5 независимых элементарных запросов.

Какие есть варианты оптимизации? Ибо запрос всего на 5 фото даёт 0.01 сек работы mysql сервера, что многовато однако, ибо в разы сложнее запросы, но не order by rand() работают в разы быстрее.

Я себе представил себе это как рекурсивную функцию которая сделает COUNT по таблице и на основании этих данных сгенерирует 5 случайных ID от 1 до COUNT и выполнит их по отдельности, если какой либо запрос не вернёт результат функция снова сгенерирует новый ID из диапозона и сделает снова запрос и так пока не будет достигнуто число нужных результатов.

Неактивен

 

#2 20.03.2009 12:13:24

Magz
Гуру
Откуда: Москва
Зарегистрирован: 18.09.2007
Сообщений: 112

Re: ORDER BY RAND в большой таблице

Я для себя придумал два алгоритма решения проблемы.
1. Получаю максимальный id в таблице, отнимаю от него количество, которое нужно получить (в данном примере sel_count = 5), генерю на php одно случайное целое число в диапазоне от 1 до (max(id) - sel_count) и делаю запрос ... WHERE id >= my_rand_id LIMIT 0, sel_count; Потом проверяю на php полученное количество, если меньше, чем sel_count, то делаю запрос WHERE id < my_rand_id LIMIT 0, (sel_count - find_count_in_first_query); и объединяю в выдаче. Вторая ситуация (когда первый запрос не вернул нужное количество) на большом количестве данных практически не встречается.
2. Завожу вторую таблицу, где два поля - id_first_table и unik_key. При добавлении и удалении записей из первой таблицы, я меняю вторую таблицу так, чтобы в unik_key все время была непрерываемая последовательность. Две таблицы нужны для того, чтобы не загружать основную (из которой идут SELECT) массовыми обновлениями ключей при удалении данных. А дальше - 5 случайных чисел в php и JOIN с первой таблицей.

Неактивен

 

#3 20.03.2009 13:26:58

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

впринципе понял, но можно кодом? Что-то не варит башка... всю ночь правил сайт с HTML 4.01 до XHTML 1.1 8\

хотелось бы коды join'a точные и "меняю вторую таблицу так..."

Отредактированно Proger (20.03.2009 13:28:09)

Неактивен

 

#4 20.03.2009 15:36:53

Magz
Гуру
Откуда: Москва
Зарегистрирован: 18.09.2007
Сообщений: 112

Re: ORDER BY RAND в большой таблице

Есть table1 с полями id и Photo, есть table2  с полями id_table1 и my_key. Соответственно, в table1 хранятся фотки, в table2 - ключи. При добавлении записи в table1 делаем

INSET INTO table2 (id_table1, my_key) VALUES (LAST_INSERTED_ID(), (SELECT max(my_key) FROM table2) + 1);
При удалении записи из table1 id удаляемой записи нам приходит в скрипт, допустим, в виде $id_for_del. Помимо удаления из table1 выполняем:

Код:

DELETE FROM table2 WHERE id_table1 = $id_for_del; 
UPDATE table2 SET my_key = my_key - 1 WHERE id_table1 > $id_for_del;

Для поиска случайных фоток генерим в php 5 случайных чисел в диапазоне от 1 до

SELECT max(my_key) FROM table2
и выполняем запрос
SELECT Photo FROM table1 JOIN table 2 ON table2.id_table1 = table1.id WHERE table2.my_key IN (...)

Индекс должен быть только на поле id_table1 - иначе будут сложности с update.

Я в своих проектах, где ORDER By RAND() нельзя использовать, использую первый вариант - вероятность получить на 10 000 записей две одинаковые (или даже с пересечением 50%) последовательности для одного пользователя незначительно мала.

P.S. Оптимизировать нужно то, что нужно оптимизировать. Если это нужно только для того, чтобы "время было не хуже, чем у других запросов" - то оставьте эту затею smile Любое усложнение логики ведет к увеличению вероятности ошибок и снижению общей надежности системы.

Отредактированно Magz (20.03.2009 15:41:51)

Неактивен

 

#5 20.03.2009 16:44:16

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

Спасибо вообщем понял вроде )) попробую реализовать - посмотрю приросты.

10 000 записей это сейчас, а рости оно может очень даже, темпы роста тоже растут и получается прогрессия однако, потому я думаю оптимизация все-таки некоторая нужна.

Оптимизирую потому что данный запрос выполняется столько же как в СУММЕ все остальные 39 с этой же страницы. Хотя имеются и запросы с LEFT JOIN и с составными ключами... все-таки не радует такая скорость, и тут к сожалению кешем не отделаешься...

Отредактированно Proger (20.03.2009 16:45:32)

Неактивен

 

#6 21.03.2009 06:54:31

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

Время выполнения лучше в 100 раз, но все же странный explain:

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     randkey     ALL     PRIMARY     NULL     NULL     NULL     4019     Using where
1     SIMPLE     photo     eq_ref     PRIMARY     PRIMARY     4     photodb.randkey.id_photo     1     
или всё нормально? Просто такое впечателние что всю таблицу randkey обрабатывает...

Отредактированно Proger (21.03.2009 06:54:50)

Неактивен

 

#7 23.03.2009 13:35:31

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: ORDER BY RAND в большой таблице

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

1. «Путь хорошей таблички».
Вспомогательная табличка должна иметь два индекса — id и photo_id. При этом id всегда
должны быть последовательны (т.е. не должно быть дырок). Тогда Вы просто выбираете случайные
числа от 1 до MAX(id) и по ним потом получаете photo_id. Проблема этого пути в поддержании
таблички в хорошем состоянии.

Удаление при этом проще всего сделать так. Пусть есть табличка
id | photo_id
00 | 00
01 | 19
02 | 37
03 | 02
Мы хотим удалить элемент с photo_id 19. Тогда мы удаляем «через последний элемент»
id | photo_id
00 | 00
01 | 02
02 | 37

2. «Через номера строк»
Тут путь проще в реализации (но немного потормознее). Нужно выбирать строки с нужным
положением в randkey. Т.е. SELECT * FROM randkey LIMIT 157,1 — выбирает 157 строку.
Объединять через UNION и потом JOINить к этому безобразию основную таблицу.

Неактивен

 

#8 23.05.2009 19:40:03

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

а как удалить "через последний элемент" лучше всего? Просто я себе ничего кроме цикла запросов не представляю, а думаю сие не оптимально.

И наверное нужно бы пример функции которая скажем раз в неделю будет анализировать таблицу на пробелы и исправлять их, но я себе опять же никак не могу смоделировать сие функцию, кроме как цикл запросов =/ HELP!

Неактивен

 

#9 24.05.2009 02:08:36

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: ORDER BY RAND в большой таблице

Цикл — зло. Двух хватит за глаза smile
Заменить + удалить.

Неактивен

 

#10 25.05.2009 10:31:41

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

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

ЗЫ пример в запросах я бы может лучше бы понял.

Неактивен

 

#11 25.05.2009 19:54:57

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: ORDER BY RAND в большой таблице

Нет, не «всем убавить на одну», а «перенести последнюю на место удаляемой».

Пример:
1 2 3 4 5 6 7 8 9 10
удаляем пятерку
1 2 3 4 10 6 7 8 9

Двигается только одно число.

Неактивен

 

#12 26.05.2009 04:38:02

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

ааа. Ну тогда запросы простейшие wink
Спасибо, а то сделать реализацию рандома я давно уже сделал и вот недавно пришлось удалить часть контента, вот и задумался а как списка рандома обновить wink

Неактивен

 

#13 26.05.2009 05:25:39

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

Только опять мне циклы снятся о.О А именно я никак не понимаю исправить когда уже расхождение 20 элементов (результат несвоевременной доработки функции удаления). Как мне найти (наиболее просто по ресурсам) эти "пропасщие ID", чтобы сместить туда последние 20. Честно подобных задач никогда не было, найти НЕсуществующие элементы. Я вижу опять же просто выгрузка всех ID, цикл и какой-то массив, который по мере прохода данных из таблицы будет заполнятся ID где разница между настоящим и предыдущим ID более 1.

Отредактированно Proger (26.05.2009 05:26:59)

Неактивен

 

#14 26.05.2009 16:18:23

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: ORDER BY RAND в большой таблице

Просто перезалейте табличку целиком? smile
Найти несуществующие можно путем левого объединения с условием:
SELECT A.id FROM A LEFT JOIN B USING (id) WHERE B.id IS NULL;

Неактивен

 

#15 27.05.2009 05:24:35

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

sad((

SQL-запрос:

SELECT  `photo`.`apid`
FROM  `photo`
LEFT JOIN  `randkey`
WHERE  `randkey`.`id_photo` IS NULL
LIMIT 0 , 10

Ответ MySQL:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `randkey`.`id_photo` IS NULL
LIMIT 0, 10' at line 1

Неактивен

 

#16 27.05.2009 13:55:36

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: ORDER BY RAND в большой таблице

Объединение нужно делать по ключу или по какому-то другому правилу.
В моем случае это USING (id). Если это не достаточно гибко, то можете
написать ON (photo.... = randkey....)

Неактивен

 

#17 27.05.2009 22:59:55

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: ORDER BY RAND в большой таблице

ааа, просто на USING INDEX он ругался, я его убрал. Сделал ON = работает.
Спасибо, чтож без вашего бы форума люди бы делали, реально самые качественные ответы!

Неактивен

 

#18 13.11.2010 23:50:49

sqluser
Участник
Зарегистрирован: 13.11.2010
Сообщений: 2

Re: ORDER BY RAND в большой таблице

Есть хорошая статья по оптимизации ORDER BY RAND() здесь

Неактивен

 

#19 14.11.2010 15:12:52

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: ORDER BY RAND в большой таблице

Угу, идея та же, что и в FAQ, но только вы действительно считаете, что кто-то
будет поддерживать статистику дырок? Ну и надо понимать, что Ваш алгоритм
сильно просаживается при, например, заведении только четных номеров.

P.S. И лучше бы создали отдельную тему — мы не убиваем ссылки по существу,
а поднимать двухгодичную тему — не стоит.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson