Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/topic.php on line 737
Форумы портала PHP.SU :: Помогите оптимизировать запрос

 PHP.SU

 Программирование на PHP, MySQL и другие веб-технологии
PHP.SU Портал     На главную страницу форума Главная     Помощь Помощь     Поиск Поиск     Поиск Яндекс Поиск Яндекс     Вакансии  Пользователи Пользователи


 Страниц (1): [1]   

> Без описания
Поиск в теме | Версия для печати
Altynayka
Отправлено: 29 Февраля, 2012 - 11:51:16
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
здравствуйте! Помогите пожалуйста оптимизировать запрос, прочитала статьи, где LIMIT 0,1.
Но у меня всегда ситуации, где LIMIT больше 1.
CODE (htmlphp):
скопировать код в буфер обмена
  1.  
  2. SELECT utt.name, utt.id FROM utt
  3. INNER JOIN nms ON nms.id = utt.id
  4. WHERE utt.id > 1  AND (nms.type = 'foto' OR nms.type = 'album') ORDER BY RAND() LIMIT 0,15
  5.  

(Отредактировано автором: 29 Февраля, 2012 - 11:53:29)

 
 Top
EuGen Администратор
Отправлено: 29 Февраля, 2012 - 12:09:09
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Altynayka
Много копий сломано вокруг выборки случайных строк в MySQL.
Некоторые полезные размышления можете найти здесь:

http://habrahabr[dot]ru/blogs/mysql/54176/

(не забудьте ознакомиться с комментариями - там тоже много полезных размышлений)


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Altynayka
Отправлено: 29 Февраля, 2012 - 15:13:45
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
не совсем понятно если честно EuGen
 
 Top
OrmaJever
Отправлено: 29 Февраля, 2012 - 15:58:33
Post Id



Активный участник


Покинул форум
Сообщений всего: 7540
Дата рег-ции: Янв. 2010  
Откуда: Чернигов


Помог: 299 раз(а)
Altynayka пишет:
не совсем понятно если честно EuGen

ORDER BY RAND() - это очень медленая конструкция, она обходит все записи и вроде бы по 2 раза, ссылку по этому поводу дал EuGen


-----
Если вы хотя бы 3-4 раза не решите всё выкинуть и начать заново - вы явно что-то делаете не так.
 
 Top
Altynayka
Отправлено: 29 Февраля, 2012 - 17:31:25
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
попробую написать, как я поняла для своего запроса:

CODE (htmlphp):
скопировать код в буфер обмена
  1.  
  2. SELECT utt.name, utt.id FROM utt INNER JOIN nms ON nms.id = utt.id
  3. WHERE utt.id > 1  AND (nms.type = 'foto' OR nms.type = 'album') ORDER BY RAND() LIMIT 0,15
  4.  
  5. $row_count = mysql_result(mysql_query("SELECT COUNT(*) FROM utt"));
  6. $query = array();
  7. while (count($query) < 15) {
  8.    $query[] = '(SELECT utt.name, utt.id FROM utt INNER JOIN nms ON nms.id = utt.id WHERE utt.id > 1  AND (nms.type = foto OR nms.type = album LIMIT '.rand($row_count).', 1)';
  9. }
  10. $query = implode(' UNION ', $query);
  11. $res = mysql_query($query);
  12.  


подскажите все ли правильно?
 
 Top
Altynayka
Отправлено: 03 Апреля, 2012 - 15:24:54
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
CODE (htmlphp):
скопировать код в буфер обмена
  1. SELECT utt.name, utt.id FROM utt
  2. INNER JOIN nms ON nms.id = utt.id
  3. WHERE utt.id > 1  AND (nms.type = 'foto' OR nms.type = 'album') ORDER BY RAND() LIMIT 0,15


оптимизировала так:

CODE (htmlphp):
скопировать код в буфер обмена
  1.        
  2.  $list = db_query("SELECT id FROM {nms}  WHERE (type = 'foto' && id > 1) || (type = 'album' && id > 1)");
  3.  $mas = array();
  4. while ( $row = db_fetch_object($list) ) {
  5.          $mas[] = $row->id;
  6.           }
  7. $mas = array_unique($mas);
  8. shuffle($mas);
  9. $mas = array_slice($mas, 1, 15);
  10. $str = join(',', $mas);
  11. $data = db_query("SELECT id, name FROM {utt} WHERE id IN ($str)");
  12. while ($result = db_fetch_object($data) ) {
  13.            echo $result->name;
  14.           }
  15.  


Время обработки уменьшилось в 6 раз, но все равно составляет порядка 10 ms. Что еще можно сделать?

(Отредактировано автором: 03 Апреля, 2012 - 15:31:18)

 
 Top
EuGen Администратор
Отправлено: 03 Апреля, 2012 - 15:27:30
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
10 ms? Не так и плохо для случайной выборки. Посмотрите, что отображает EXPLAIN SELECT (хотя, думаю, с ним все будет в порядке)
Цитата:
$mas = array_unique($mas);

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


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Altynayka
Отправлено: 03 Апреля, 2012 - 15:58:28
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
да, но он как-то скачет от 10 до 20. "explain" -обязательно попробую.
нет не первичный ключ в том то и дело, убрала связку с другой таблицей, где и был первичным ключом, но из-за этого время увеличивается.
Цитата:
Затем, выборка всех id - требует много памяти. И это тоже может ухудшать производительность.
- я понимаю, но пропадет весь смысл в случайной выборке...

(Отредактировано автором: 03 Апреля, 2012 - 17:49:37)

 
 Top
Altynayka
Отправлено: 03 Апреля, 2012 - 20:56:31
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
Ломаю голову, как можно оптимизировать:
CODE (htmlphp):
скопировать код в буфер обмена
  1.  $list = db_query("SELECT id FROM {nms}  WHERE (type = 'foto' && id > 1) || (type = 'album' && id > 1)");

А вообще можно?
 
 Top
Мелкий Супермодератор
Отправлено: 03 Апреля, 2012 - 21:07:12
Post Id



Активный участник


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT id FROM {nms}  WHERE type IN ('foto', 'album') && id > 1

Но почти наверняка выполняется в обоих случаях один и тот же запрос.

Индекс по id & type создан? Попробуйте ещё индекс по type & id


-----
PostgreSQL DBA
 
 Top
Altynayka
Отправлено: 03 Апреля, 2012 - 21:31:50
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
Мелкий пишет:
Индекс по id & type создан? Попробуйте ещё индекс по type & id

Спасибо, но при составном индексе в обоих случаях время увеличилось. И с запросом - также. Огорчение

(Отредактировано автором: 03 Апреля, 2012 - 21:33:13)

 
 Top
Мелкий Супермодератор
Отправлено: 03 Апреля, 2012 - 21:45:36
Post Id



Активный участник


Покинул форум
Сообщений всего: 11926
Дата рег-ции: Июль 2009  
Откуда: Россия, Санкт-Петербург


Помог: 618 раз(а)
Смотрите, что explain пишет, какой индекс использует, из каких выбирает, сколько строк затрагивает. Сколько вообще строк возвращается. может, эффективнее будет select distinct, вместо array_unique


-----
PostgreSQL DBA
 
 Top
Altynayka
Отправлено: 05 Апреля, 2012 - 11:37:24
Post Id


Частый гость


Покинул форум
Сообщений всего: 208
Дата рег-ции: Март 2008  


Помог: 0 раз(а)
Select distinct - пробовала -время увеличивается.
 
 Top
EuGen Администратор
Отправлено: 05 Апреля, 2012 - 12:42:50
Post Id


Профессионал


Покинул форум
Сообщений всего: 9095
Дата рег-ции: Июнь 2007  
Откуда: Berlin


Помог: 707 раз(а)
Altynayka
Вообще, задача "выбрать случайные N строк из таблицы" - несмотря на простую формулировку, не имеет простого решения.
По поводу Вашего решения - на недостатки я указывал - ведь Вы загружаете в память весь набор идентификаторов, и потому работа замедляется.
Существует несколько подходов к решению задачи, но нет "идеального". Среди решений я бы выделил два основных направления:
- Использующие только SQL. К таким относятся ORDER BY RAND() и все подобное. Впрочем, удалось найти и более оригинальные решения, например
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT pk, DATA
  2. FROM test AS r1 JOIN
  3.   (SELECT (RAND() *
  4.     (SELECT MAX(pk)
  5.     FROM test)) AS id)
  6.   AS r2
  7. WHERE r1.pk >= r2.id
  8. ORDER BY r1.pk ASC
  9. LIMIT 1;

этот вариант будет существенно быстрее, однако же он дает возможность выбрать лишь одну запись, и таким образом, в случае N записей сложность возрастает линейно.

- Варианты, использующие различные внешние средства. Например, php-генерация запроса.
Первый из них - использовать UNION и LIMIT. Подойдет только для небольших таблиц, так как LIMIT M,1 заставляет СУБД выбирать M строк с тем, чтобы потом оставить одну, то есть производительность ухудшается линейно с увеличением размера таблицы:
PHP:
скопировать код в буфер обмена
  1. $iMax     = mysql_result(mysql_query('SELECT COUNT(*) FROM test'), 0);
  2. $iCount   = 10;
  3. $rgResult = array();
  4. $sQuery   = '';
  5. array_walk(array_fill(0, $iCount, ''), function($mValue) use (&$rgResult, $iMax)
  6. {
  7.    while(in_array($iValue=mt_rand(0, $iMax), $rgResult));
  8.    $rgResult[]=$iValue;
  9. });
  10. $sQuery=join(' UNION '."\n", array_map(function($x)
  11. {
  12.    return 'SELECT * FROM test LIMIT '.(int)$x.', 1'."\n";
  13. }, $rgResult));
  14. //var_dump($sQuery);

минус его еще и в том, что SELECT COUNT(*) должен быть строго привязан к последующему коду, что порождает необходимость блокировки таблицы (иначе может случиться так, что сгенерируется случайное число, равное максимальному а за время обработки данных в массиве запись с этим номером была уже удалена)
Следующий - хороший на мой взгляд, вариант - вводить дополнительное поле, которое будет являться номером строки "как есть", то есть будет всегда идти по порядку. В этом случае худшее, что есть в предыдущем варианте - а именно выборку по LIMIT (да впридачу еще и с UNION) - можно заменить на выборку по ключу, с IN - а это куда быстрее. Минус этого подхода очевиден - это введение дополнительного ключа (что замедляет операции вставки) а так же необходимость следить за соблюдением правильной нумерации. Это приводит либо к введению триггеров, либо к контролю за полем на уровне того же PHP.
И последний вариант, который, по моему мнению, заслуживает внимания - это перемешивание записей таблицы регулярно (например, постановка в планировщик cron). Тогда исходная задача сведется к выборке N подряд записей начиная со случайной. Либо же, если перемешивание идет часто, N подряд просто с 1-й записи. Минус тоже легко заметить - способ непригоден для активно используемых таблиц и для проектов с загрузкой "24/7", так как перемешать записи в таблице - весьма ресурсоемкая операция.


-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.
 
 Top
Поиск в теме | Версия для печати
Страниц (1): [1]
Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)
« SQL и Архитектура БД »


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



Powered by PHP  Powered By MySQL  Powered by Nginx  Valid CSS  RSS

 
Powered by ExBB FM 1.0 RC1. InvisionExBB