1 Востаннє редагувалося ConTrast77 (27.03.2015 22:26:33)

Тема: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

Шукаю варіант рішення моєї задачі.
Повернувся до свого робочого сайту і хочу доробити.


!!!_ У мене є таблиця БД MySQL 5,
- в таблиці новини йдуть підряд,
- є ключовий стовпчик (аутоінкрементний) в таблиці
- є нумерований стовпчик UNIQUE, нехай назва NNN
- новини починаются з номеру NNN >= 99

Що хочу:
зараз 6-ть новин, хочу робити багато новин, і щоб вони при запиту до сторінки відкривались рандомно 6-ть аби яких новин.



1. перший варіант - робити рандом пошук кожної строки в Таблиці БД і так 6-ть раз на кожне відкривання сторінки

SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;

http://akinas.com/pages/en/blog/mysql_random_row/

2. другий варіант, запит до БД, для отримання деяких даних MAX(NNN) , COUNT(*), і інших... а потім створювати рандом 6-ти чисел у PHP, потім новий один запит типу. Таким чином хоч я один буду керувати змінами новин, але вважаю не достить кмітливим, з боку запитів SQL, тобто в теорії, доки зроблю обчислення 6-ти номерів строк у БД, хтось інший може їх змінити (видалити), тобто хочу розуміти на перспективу і не робити таку каку:

====== 2.1. ====
http://biznesguide.ru/coding/172.html

$offset_result = mysql_query("SELECT MAX(id) AS max, MIN(id) AS min FROM `table`");
$offset_row = mysql_fetch_object( $offset_result );
$max = $offset_row->max;
$min = $offset_row->min;
 
$n = 5; // кількість записів, для отримання
$i = 0;

$ids = array();

while($i < $n){
  $ids[] = mt_rand($min,$max);
}
 
$result = mysql_query( "SELECT * FROM `table` WHERE id IN(".implode(',',$ids).") LIMIT ".$n);

===== 2.2 ==========
SQL .. MAX(NNN) , COUNT(*)
PHP .. $nnn=array[10000, 60000, 110000, 160000, 210000, 260000, 310000, 360000, 410000, 460000] - після створення значень, але поза SQL
SQL .. SELECT id FROM table1 WHERE nnn IN ($nnn[10000], ..... 60000, 110000, 160000, 210000, 260000, 310000, 360000, 410000, 460000);
.... приклад був у коментах тут - http://habrahabr.ru/post/54176/



3. щось цікаве знайшов
тут - http://www.askdev.ru/question/20399/%D0 … %BE%D0%BA/
http://stackoverflow.com/questions/4329 … -rows-fast
http://www.warpconduit.net/2011/03/23/s … k-results/ - !

Multiple Rows at once - http://jan.kneschke.de/projects/mysql/order-by-rand/
..на Рос.: http://hudson.su/2010/09/16/mysql-optim … r-by-rand/

і тут - http://habrahabr.ru/post/207096/
http://alexvolkov.ru/mysql-rand-how-to- … om-db.html
http://biznesguide.ru/coding/172.html


Як мені бути, яким шляхом йти??? які вимоги для колонок обов'язкові? Індекс*?
1) http://habrahabr.ru/post/54176/ - тут є приклад об'єднання шляхом UNION , за методом SQL та є SQL + PHP
2) якось можна методом WHERE nnn IN тільки SQL з розширенням коду? щоб все влізло тільки в SQL ? та швидко шукало ..

2 Востаннє редагувалося ConTrast77 (16.03.2015 04:43:36)

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

Невже все так просто і не треба бути "завумним", якщо поля індексовані?

The prior postings on random rows selections have shown:

SELECT * FROM foo ORDER BY count*RAND() LIMIT 5;

швидкість за рахунок команди ORDER BY і кожен рядок рандомний, тільки потім ліміт 5 ???

3 Востаннє редагувалося Regen (16.03.2015 15:22:03)

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

мав схожу проблему, яка уже обговорювалась на форумі
ось лінк http://replace.org.ua/topic/3428/ (на остадній сторінці я усе описав)

Don’t do as I have done
Win where I have lost
Have where I have none

4

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

ConTrast77 написав:

Шукаю варіант рішення моєї задачі.
Повернувся до свого робочого сайту і хочу доробити.
......
Як мені бути, яким шляхом йти??? які вимоги для колонок обов'язкові? Індекс*?
.... ..

Ти провів такий широкий аналіз матеріалу, коментарів, такі і не визначився?
Бери тоді перший ліпший варіант хоча б той же Хабр і не морочи собі голову :)

Прихований текст

CREATE PROCEDURE `spRandomSelect`(IN aSchema VARCHAR(50), IN aTable VARCHAR(50), IN aNumRows INTEGER(11))
    NOT DETERMINISTIC
    READS SQL DАТА
BEGIN
  DECLARE iQuery VARCHAR(10000);
  DECLARE iNumRows INTEGER(11);

  SET iNumRows = (SELECT `TABLE_ROWS` FROM `information_schema`.`TABLES` t
    WHERE t.`TABLE_SCHEMA` = aSchema AND t.`TABLE_NAME` = aTable);
  SET iQuery = '';
  loop1: LOOP
    SET iQuery = CONCAT(iQuery, '(SELECT * FROM `', aSchema, '`.`', aTable,
      '` LIMIT ', ROUND(RAND(UNIX_TIMESTAMP() + aNumRows) * iNumRows), ', 1)');
    IF aNumRows > 1 THEN
      SET iQuery = CONCAT(iQuery, ' UNION ');
    END IF;
    SET aNumRows = aNumRows - 1;
    IF aNumRows > 0 THEN
      ITERATE loop1;
    END IF;
    LEAVE loop1;
  END LOOP loop1;
  SET @iQuery = iQuery;
  PREPARE iExecStmt FROM @iQuery;
  EXECUTE iExecStmt;
  DRОP PREPARE iExecStmt;
END;

Збираю знання і... роздаю знання :)

5 Востаннє редагувалося ConTrast77 (18.03.2015 12:45:21)

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

Після вашої рекомендації вивчив і повністю проконспектував примірник з Хабру.
Це навіть удосконалило і поширило деякі знання з SQL.

Але наткнувся на цей код з Хабр примірника, і вважаю його хибним, може помиляюсь:?

Анатолій написав:

... хоча б той же Хабр і не морочи собі голову

    SET iQuery = CONCAT(iQuery, '(SELECT * FROM `', aSchema, '`.`', aTable,
      '` LIMIT ', ROUND(RAND(UNIX_TIMESTAMP() + aNumRows) * iNumRows), ', 1)');

ROUND(RAND(UNIX_TIMESTAMP() + aNumRows) * iNumRows)

- якщо беремо, що у нас є вибірка (великої) кількості рядків, і iNumRows це обчислена кількість рядків (нехай у нас це 100500 знайдених рядків), майже як COUNT(*), тоді формула безглузда
- aNumRows - вважаю, що це кількість шуканих ВПАДКОВИХ (РАНДОМ) рядків, їх кількість (нехай у нас 10)
- UNIX_TIMESTAMP() - так розумію що це використання часу, для перероблення під РАНДОМ, і використання отриманого часу як змінна у вигляді INT , нехай видало 882226357, але обнулення раз на 100 рік ?! чи навіть раз на Добу ?! )))

Виходить приклад:

ROUND(RAND(882226357 + 10) * 100500)

тоді він в голову не лізе!
Це вихід за межі пошукового об'єму - закреслив
Це матиме майже статичне значення... посеред всієї вибірки

Подякували: Анатолій1

6

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

1. І що ж знайшли кінцевий варіант, що вас влаштовує?
2. Якщо є варіант і вас не влаштовує, то можна в студію озвучити, які саме критерії вас не влаштовують, цікаво
( при нагоді  поставлю собі галочку розв'язати подібну задачку)

Збираю знання і... роздаю знання :)

7

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

ConTrast77 написав:

Після вашої рекомендації вивчив і повністю проконспектував примірник з Хабру.
Це навіть удосконалило і поширило деякі знання з SQL.

Але наткнувся на цей код з Хабр примірника, і вважаю його хибним, може помиляюсь:?

Анатолій написав:

... хоча б той же Хабр і не морочи собі голову

    SET iQuery = CONCAT(iQuery, '(SELECT * FROM `', aSchema, '`.`', aTable,
      '` LIMIT ', ROUND(RAND(UNIX_TIMESTAMP() + aNumRows) * iNumRows), ', 1)');

ROUND(RAND(UNIX_TIMESTAMP() + aNumRows) * iNumRows)

- якщо беремо, що у нас є вибірка (великої) кількості рядків, і iNumRows це обчислена кількість рядків (нехай у нас це 100500 знайдених рядків), майже як COUNT(*), тоді формула безглузда
- aNumRows - вважаю, що це кількість шуканих ВПАДКОВИХ (РАНДОМ) рядків, їх кількість (нехай у нас 10)
- UNIX_TIMESTAMP() - так розумію що це використання часу, для перероблення під РАНДОМ, і використання отриманого часу як змінна у вигляді INT , нехай видало 882226357, але обнулення раз на 100 рік ?! чи навіть раз на Добу ?! )))

Виходить приклад:

ROUND(RAND(882226357 + 10) * 100500)

тоді він в голову не лізе!
Це вихід за межі пошукового об'єму - закреслив
Це матиме майже статичне значення... посеред всієї вибірки

RAND повертає число від 0 до 1, а параметр його використовується для того, щоб при різних викликах мати різні значення.

МАКЕ ЦКЯАІИЕ БЯЕАТ АБАІИ

8 Востаннє редагувалося ConTrast77 (18.03.2015 19:18:44)

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

Анатолій написав:

... і вас не влаштовує, то можна в студію озвучити, які саме критерії вас не влаштовують, цікаво


Мабуть зроблю так (деякі умови повторю з першого повідомлення):
- Є таблиця, з індексованим стовпчиком NNN,
- дані INT, у стовпчику NNN не послідовні і можуть бути з пропусками: 1, 3, 55, 60
- при рандом-вибірки, слідувати умовам: що NNN >99 та activate="A", тобто активні новини які мають індекс не менше 100
- не використовувати ORDER BY rand()
- підвищити швидкість якщо можливо.


Рішення:
- Створення процедури , яка створить рядок для виконання пошуку по значенням з допомогою оператора переліку: IN (...)
.... тобто SELECT news FROM table1 WHERE nnn IN (3,55,......);
- ІN (...) створити без допомоги рандому PHP, а тільки засобами SQL

- МОЯ невизначеність.  *SCRATCH*  Я не знаю як працює процедура тут ==>. Якщо звертаюсь до процедури, то доки вона виконується - паралельно інша особа із своїм зверненням до БД чекає чи виконує свої дії?
... я б хотів закрити можливість зміни кількість рядків таблиці table1 або значення активності activate="A" доки не виконаю рандом-пошук.

Рішення, вдосконалення #1:
- створення додаткової таблиці-індексу, вона матиме посилання тільки на ті рядки основної таблиці, якщо там виконується умова: рядок з номером не пропущений (існує), NNN >99 та activate="A"
- ця створена додаткова таблиця з зовнішнім індексом на таблицю table1, для легкого визначення рандомних 6-ти рядків, якщо умови table2.ID=RAND()*кількість_рядків_ID/COUNT?/ та  table2.KEY = table1.NNN

Рішення, вдосконалення #2:
- Створення тригерів, CREATE TRIGGER, які б реагували на додання новин, зміни активності, видалення рядка з новиною. І відповідним коригуванням додаткової таблиці з зовнішнім індексом. (Збільшує час на редагування новин), але зберігає цілісність (реляційну БД). Не впливає на рандомний пошук після вдосконалення #1.


Рішення, вдосконалення #3:
Це дійсно нахабство після умов #2 і #1, але хочу ще зрозуміти і навіть створити (експеримент)...
- окрему таблицю, у якій будуть зберігатись 6-ть рандомних новин
- цю таблицю оновлювати раз на добу, але НЕ можливостями PHP, а чи є ЯКИЙСЬ засіб самовільної процедури у SQL яка б самостійно , раз на добу вмішувалась в рядки якоїсь таблиці і змінювала. Тобто чи є часовий тригер у SQL?

9

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

ConTrast77 написав:

....
Мабуть зроблю так (деякі умови повторю з першого повідомлення):
....

моя думка .... сильно вирішуєш задачу, глибоко копаєш.... змістовно

але я так зрозумів одного рішення поки що немає,

часовий запит?... недавно теж цікавився, але крім cron ніхто більше нічим не підказав, але я все ще далекий до розуміння cron-у :)

можна тоді попросити, якщо звичайно не складно, ти все таки аналізуєш, шукаєш переваги і недоліки різних стилів кодування, то в кінці зробити висновок;
код ось-такий,
переваги його ось-такі,
швидкість його більша через те, що...
я зараз просто дещо іншим зайнятий, але маю бажання проаналізувати твою роботу і схожі напрямки щоб розібратись в рандомній вибірці даних

Збираю знання і... роздаю знання :)

10 Востаннє редагувалося ConTrast77 (26.03.2015 00:02:34)

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

1. Створення таблиці, загальним розміром 600000 рядків ( без 2 рядків):
з донорів цієї таблиці (було 7 рядків), для наповнення БД, замість PHP-запитів навчився створювати процедури всередині самої БД

>> Запит був успішно виконаний ( запит зайняв 49.0361 сек. )

Обсяг таблиці склав, у вигляді файлів: 5,87 МБ + 3,21 ГБ (3450845640 + 6158336 = 3457003976 байт, = 3296,856 МБ ),
.. на більше не вистачає об'єму логічного диска


2. об'єм даних повторюється кожні 7 рядків таблиці й дорівнює заповненню по-символам, приблизно, 39,4 КБ (40 385 байт), формат Win-1251
У таблиці (у кожному рядку) використовуються 6 текстових стовпчиків і 3 службових, ця інформація забезпечить розуміння, що пошук складний (з зміненими по розміру рядками).
Текстові рядки складаються з: 2 стовпчика "varchar(80)" і 4 стовпчика "text"


3. Перший запит ( перехід до останньої 30-тки рядків)

SELECT * 
FROM  `news2` 
LIMIT 599970, 30

>> зайняв ледве більше 10 секунд,
>> усі інші запити, по зсуву 30 у будь-яку сторону - (599,998 усього записів/рядків таблиці, запит зайняв 1.1660 сек.)

випадковий запит

SELECT * 
FROM  `news2` 
LIMIT 32000, 30

>> видало (30 усього, запит зайняв 0.0685 сек.)

випадковий запит
SELECT *
FROM  `news2`
LIMIT 320000, 30
>> видало (30 усього, запит зайняв 0.6230 сек.)

Подякували: Анатолій1

11 Востаннє редагувалося ConTrast77 (26.03.2015 01:11:14)

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

Тепер подивимось перший самий простий запит, рандомної вибірки:

SELECT nn, nn2 FROM table1 WHERE nnn='A' ORDER BY RAND() LIMIT 1

та переробимо його до моєї  потреби (вибірка 6-ти випадкових рядків) за цим принципом.

Запит нестерпно повільний, навіть потім перевірив у простій таблиці з 7-ми рядків.
Процес займав нескінченність. Пару разів переоновлював, перезавантажував запит.
Потім ще разок спробував - процес зайняв аж 58 секунд!!!
повторний запит - 42 секунд!!! навіть не знаю, куди він буферизував вибірку (перебірка таблиці), вінчестер інтенсивно пішов в навантаження роботою

SELECT nn, nn2 FROM table1 WHERE nnn='A' ORDER BY RAND() LIMIT 6

>> Отображает строки 0 - 5 (6 всего, запрос занял 57.6254 сек.)
>> Отображает строки 0 - 5 (6 всего, запрос занял 42.1137 сек.)

Потім зробилось іншого разу щось дивовижне, мабуть були скинуті зайві довгі запити (як ті, що кладуть сервер), швидкість ось така:

>> Отображает строки 0 - 5 (6 всего, запрос занял 26.1509 сек.)
>> Отображает строки 0 - 5 (6 всего, запрос занял 1.6966 сек.)
>> Отображает строки 0 - 5 (6 всего, запрос занял 1.6898 сек.)


Такий запит взагалі повільний на MySQL ( сервер 5.1.62, клієнт 5.0.8):
- хоч з ORDER BY , хоч без нього

SELECT id, nn2, nn3
FROM table1
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM table1 )
LIMIT 1

>> Отображает строки 0 - 0 (1 всего, запрос занял 72.4891 сек.)
>> Отображает строки 0 - 0 (1 всего, запрос занял 90.1927 сек.)

Подякували: Анатолій1

12 Востаннє редагувалося ConTrast77 (26.03.2015 01:07:11)

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

Наступний приклад показує елементи, послідовні - "підряд".
Обчислення випадковості, виконується тільки 1 раз для 1-го стовпчика,
інші вибірки рядків ідуть послідовним "перебиранням" до наступних рядків з таблиці донора,
якщо знач. >= від початкового.

>>> У такому випадку рядка вибірки йдуть "підряд" без особливого перескакування по таблиці донора
(коли значення ID донора упорядковано, переважно на збільшення, і значення цього стовпчика зберігаються не хаотично).

Швидкість вибірки  0.0115-0.0336 секунд, виконання перебирання швидкістю 0.0336 - у випадках - перехід до рядків на "далеку відстань"
(від попереднього місця попередньо знайденого значення по таблиці-донору).

SELECT  id, nn2, nn3
FROM table1 AS newstabl
      JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM table1)) AS id) AS temp
WHERE newstabl.id >= temp.id  AND newstabl.nnn='A' LIMIT 6;

>> Відображає рядка 0 - 5 (6 усього, запит зайняв 0.0271 сек.)
>> Відображає рядка 0 - 5 (6 усього, запит зайняв 0.0293 сек.)
>> Відображає рядка 0 - 5 (6 усього, запит зайняв 0.0336 сек.)
>> Відображає рядка 0 - 5 (6 усього, запит зайняв 0.2862 сек.)
>> Відображає рядка 0 - 5 (6 усього, запит зайняв 0.0168 сек.)
>> Відображає рядка 0 - 5 (6 усього, запит зайняв 0.0115 сек.)

Приклад не особливий, він взятий як і попередні - ось тут:
http://www.warpconduit.net/2011/03/23/s … k-results/
в даному випадку: Inner Join Method

Подякували: Анатолій1

13

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

ConTrast77 написав:

1. Створення таблиці, загальним розміром 600000 рядків ( без 2 рядків):
з донорів цієї таблиці (було 7 рядків), для наповнення БД, замість PHP-запитів навчився створювати процедури всередині самої БД

Я так розумію таку таблицю ти створив чисто для тестування. На швидкість виконання оптимізованого запиту.
А практична таблиця скільки із скількох записів складатиметься? І якщо мова йде про новини то чи всі новини актуальні? Можливо новина що йде за номером 155 678 вже не актуальна.
Я до того веду що якщо таблиця складатиметься з 2 000 записів, то вибірка даних відбуватиметься досить швидко, скажімо  тих же 0,5 секунди плюс мінус долі, що напевно не будуть суттєвими.
Чи мова все ж таки йде за максимальне зменшення часу вибірки в любому випадку.

Збираю знання і... роздаю знання :)

14

Re: MySQL, вибірка випадкових 6-ти рядків RAND() +швидкість

Я трохи така людина, яка якщо береться і є можливість писати зразу, досконало - тоді замість всієї картини-скелету сайту - влізу саме в ту проблему, на скільки є можливість вдосконалити код, до умовного універсалу згідно потреб.

Тобто якщо зроблю запит, який зможе приблизно однаково працювати хоч з таблицею на 150 новин, хоч на пів мільйону, тоді і результат, і може згодиться в тому напрямку як йду. Тобто до більшої професійності.

Коли почав шукати рішення - було достатньо саме першого варіанту! і його могло б вистачити навіть на 3 роки!
Якщо сам би писав ті новини, 1 новину за день. Тобто 1000 новин = три роки власного контенту!
А оскільки контент пишеться в новинах, не частіше 1 новина на тиждень, а ще паче - 1-2 новини на місяць. То самі розумієте на якому прикладі я міг би зупинитись для власних потреб. ))))

P.S. у вас на сайті мій запит, на гостьовій (запит прийшов на пре-модерацію). Якщо зацікавить - то товаришування за скайпом, я б мав за щирість.