Перейти к контенту

Оптимизация SQL запроса

Borland_delphi_6

Автор: Borland_delphi_6,
в Программирование

 Поделиться

Рекомендуемые сообщения

Borland_delphi_6

Borland_delphi_6

Опубликовано
Опубликовано

Пытаюсь заставить IPB-шную галерею выводить картинки на индексе случайным образом. Первое, что приходит в голову - делать выборку

 

SELECT id FROM ibf_gallery_images WHERE category_id = X ORDER BY RAND() LIMIT 1;

 

Но так уж почему-то исторически сложилось, что половина изображений в этой таблице не имеют никаких связей в поле category_id => под выборку они не подпадают => вариант не прокатывает. Зато у них заполнено поле album_id, а в таблице ibf_gallery_albums есть соответствие между album_id и category_id. Таким образом, зная ID изображения, мы можем выйти на category_id (что мне как раз и нужно) через ibf_gallery_albums. Вывод: нужны вложенные запросы. Для меня эта область темная, так что прошу вашей помощи:

 

 

SELECT id from ibf_gallery_images WHERE album_id = (SELECT id FROM ibf_gallery_albums WHERE category_id = '6' LIMIT 1) ORDER BY RAND()

 

Так работает быстро (0.00 - 0.02), но возвращает случайные ID только из одного альбома.

 

А вот так:

 

 

SELECT id from ibf_gallery_images WHERE album_id = (SELECT id FROM ibf_gallery_albums WHERE category_id = '6' ORDER BY RAND() LIMIT 1) ORDER BY RAND()

 

Работает о-о-о-чень долго (1 сек на запрос), но возвращает именно то, что нужно.

 

При этом сам по себе запрос

 

SELECT id FROM ibf_gallery_albums WHERE category_id = '6' ORDER BY RAND() LIMIT 1

 

выполняется с нормальной скоростью, а вот когда он становится вложенным...

 

Подскажите, как переделать этот запрос таким образом, чтобы он выполнялся с человеческой скоростью?

Ссылка на комментарий
Поделиться на других сайтах
Arhar

Arhar

Опубликовано
Опубликовано

я эту галерею из-за этой исторической особенности переписал почти всю)

при добавлении фотографии в альбом обязательно проверяю ид категории в которой альбом, и пишу в таблицу картинок и тот ид, и другой

после успешного тестирования сделал скрипт, который пересчитал для всех фотографий, у которых были нули в соответственном поле данные заново и теперь у меня этой проблемы нет по умолчанию...

как вариант - сделать именно так и ничего вкладывать не надо будет

Ссылка на комментарий
Поделиться на других сайтах
Borland_delphi_6

Borland_delphi_6

Опубликовано
  • Автор
Опубликовано
Терь уже поздняк метаться. Галерея, конечно, кривая до не могу, но что есть - то есть. Вернемся к сабжу.
Ссылка на комментарий
Поделиться на других сайтах
Arhar

Arhar

Опубликовано
Опубликовано

Тогда делать 2 запроса

первый - 

SELECT id FROM albums WHERE category_id=6

создать массив идешек

массив implode через запятую

второй - 

SELECT id FROM images WHERE category_id=6 OR album_id IN('.$arr.') ORDER BY RAND() LIMIT 1

 

 

 

 

P.S.ну смотря какой поздняк

у меня на тот момент уже было 3000 фоток...

 

какая версия галереи?

почему бы и не сделать так?

Ссылка на комментарий
Поделиться на других сайтах
Borland_delphi_6

Borland_delphi_6

Опубликовано
  • Автор
Опубликовано
Наши посетители загрузили 7977 изображений и отправили 3738 комментариев.

 

Версия 2.1. Не хочу исправлять баги IPS, надеюсь, в новой версии повеселее будет, а пока хочется реализовать сабж своими силами, не прибегая к изменениям существующей БД.

Ссылка на комментарий
Поделиться на других сайтах
Borland_delphi_6

Borland_delphi_6

Опубликовано
  • Автор
Опубликовано
В принципе, сейчас попробую через два, думаю, получится, но ведь по-идеи это можно и через один сделать как-то? :D Если два по-отдельности работают быстро, почему не могу быстро работать вместе? ;)
Ссылка на комментарий
Поделиться на других сайтах
Borland_delphi_6

Borland_delphi_6

Опубликовано
  • Автор
Опубликовано

Блин. Что-то я не осилил короче. Выборка получилась, но работала криво + я еще в последний момент сообразил, что необходимо сравнивать разрешения для отдельного пользователя, а откуда их выдрать и как с ними составить SQL - мне стало лень думать. Столько заморочек = ( Я расчитывал на банальный ORDER BY RAND()...

 

Ежели кому интересно довести до ума, вот то, до чего я дошел:

 

			$cat_sql = mysql_query("SELECT id FROM ibf_gallery_albums WHERE category_id = '".$row['id']."' ORDER BY RAND() LIMIT 1");
		$albums[$k] = @mysql_fetch_array($cat_sql);
		if ($albums[$k]['id'])
		{
		$image_id_sql = mysql_query("SELECT id from ibf_gallery_images WHERE album_id = ".$albums[$k]['id']." ORDER BY RAND() LIMIT 1");
		$ids = @mysql_fetch_array($image_id_sql);
		if (!$ids['id']) { $ids['id'] = $row['last_pic']; }
		//echo "cat[".$row['id']."]".$albums[$row['id']]['id']."=".$ids['id']."; ";
		}
		$k++;
		$ids = @mysql_fetch_array($sql_samp2);
		//echo $albums[$row['id']]['id']." ";
		if( $check_permissions && ! $this->ipsclass->check_perms( $row['perms_thumbs'] ) )
		{
				$this->allowed_cats--;
				continue;
		}

					$this->image_ids[ $row['id'] ] = $ids['id'];//$row['last_pic'];

 

Это в lib_categories.php, в районе 120-130 строки нужно вставить. Там где цикл обработки выборки начинается.

Ссылка на комментарий
Поделиться на других сайтах
Arhar

Arhar

Опубликовано
Опубликовано

довести запросто можно)

из категорий брать пермишн просмотра)

я это тоже дописывал в своей галерее где-то, ща скопипастю...

 

add

ух там жесть)

 

	function read_data ($check_permissions = true, $root='', $parent=0)
{
	$this->ipsclass->DB->simple_construct(array('select'=>'id,category_id',
												'from'  =>'gallery_albums',
												));
	$this->ipsclass->DB->simple_exec();
	while($a = $this->ipsclass->DB->fetch_row())
	{
		$aids[]=$a['id'];
	}
	if(count($aids>0))
	{
		$whereaids=implode(',',$aids);
		$this->ipsclass->DB->simple_construct(array('select'=>'id,category_id',
													'from'  =>'gallery_images',
													'where' =>'album_id IN ('.$whereaids.') OR (album_id=0 AND category_id>0)',
													'group' =>'category_id',
													'order' =>'RAND()',
													));
		$this->ipsclass->DB->simple_exec();
		while($i = $this->ipsclass->DB->fetch_row())
		{
			$cids[$i['category_id']]['image']=$i['id'];
		}			
	}
	// Read the data from the database
	$this->ipsclass->DB->cache_add_query( 'get_all_categories', '', 'gallery_sql_queries' );									 
	$this->ipsclass->DB->simple_exec();  

	/**
	* Any allowed cats?
	**/
	$this->allowed_cats = $this->ipsclass->DB->get_num_rows();

	while ($row = $this->ipsclass->DB->fetch_row())
	{
		if( $check_permissions && ! $this->ipsclass->check_perms( $row['perms_thumbs'] ) )
		{
			$this->allowed_cats--;
			continue;
		}

		$this->image_ids[ $row['id'] ] = $row['last_pic'];
		if($cids[$row['id']]['image']>0)
		{
			$this->image_ids[ $row['id'] ] = $cids[$row['id']]['image'];
		}

		if( $this->restrict && !$row['album_mode'] )
		{
			$this->allowed_cats--;
			continue;
		}

		if (!isset($this->data[ $row['id'] ])) 
		{
			$this->data[ $row['id'] ] = $row;
		}
		else 
		{
			$this->data[ $row['id'] ] = array_merge($this->data[ $row['id'] ],$row);				
		}

		if( $parent == $row['parent'] )
		{
			$this->ordered[] =& $this->data[ $row['id'] ];
		}

		// Let the parent category know they have a child
		$this->data[ $row['parent'] ]['child'][] = $row['id'];

		// Add our own id & our descendants to all the categories above
		if (isset($this->data[ $row['id'] ]['descendants']))
		{
			$to_add = array_merge($this->data[ $row['id'] ]['descendants'], array($row['id']));
		}
		else
		{
			$to_add = array($row['id']);
		}

		//$this->_set_decendants($row['parent'],$to_add);
	}
	$this->data[0]['name'] = $root;
}

Ссылка на комментарий
Поделиться на других сайтах
Borland_delphi_6

Borland_delphi_6

Опубликовано
  • Автор
Опубликовано

Я тут наднях опять заморочил свой мозг на тему и понял, что выбирать категории по параметру доступные/недоступные нет смысла: если пользователь не знает о существовании какой-то скрытой категории, то и картинку он не увидет (просто негде), а если знает, но открыть не может - так и быть, пускай на thumb'ы любуется, слюни пускает. Пожалуй, еще поковыряюсь с запросами, правда, пока не понятно, почему мой код, приведенный выше иногда дает сбой...

 

(хочу отладчик... :D )

Ссылка на комментарий
Поделиться на других сайтах
xRay

xRay

Опубликовано
Опубликовано
(хочу отладчик... :D )

Дык а в чем проблема? Zend Studio, NuSphere PHPEd, Aptana и т.д.

Отлаживать можно точно так же как в Делфях (не показывается тольк асемблерный код)

Вот тут глянь мы с Song тему затрагивали: http://www.ibresource.ru/forums/index.php?showtopic=48359

Ссылка на комментарий
Поделиться на других сайтах
Borland_delphi_6

Borland_delphi_6

Опубликовано
  • Автор
Опубликовано

Фух. Довел код до конца:

 

	function read_data ($check_permissions = true, $root='', $parent=0)
{
	// Read the data from the database
	$this->ipsclass->DB->cache_add_query( 'get_all_categories', '', 'gallery_sql_queries' );																		 
	$this->ipsclass->DB->simple_exec();

	/**
	* Any allowed cats?
	**/
	$this->allowed_cats = $this->ipsclass->DB->get_num_rows();

	while ($row = $this->ipsclass->DB->fetch_row())
	{
		if ($row['album_mode'])
			{
			/*
			Если категория предназначения для размещения альбомов - сперва делаем
			выборку альбомов данной категории, затем делаем выборку изображений из
			данных альбомов.
			*/
			$cat_sql = mysql_query("SELECT id FROM ibf_gallery_albums WHERE category_id = '".$row['id']."' AND public_album = 1 ORDER BY RAND() LIMIT 1");
			//if (mysql_error()){  echo "SELECT FROM ALBUMS: ".mysql_error()."<br>"; } //DEBUG
			$albums[$row['id']] = mysql_fetch_array($cat_sql);

			//if (mysql_error()){  echo "FETCH ALBUMS: ".mysql_error()."<br>"; } //DEBUG
			$images_id_sql = mysql_query("SELECT id from ibf_gallery_images WHERE album_id = ".$albums[$row['id']]['id']." AND approved = 1 ORDER BY RAND() LIMIT 1");
			//if (mysql_error()){  echo "SELECT FROM IMAGES: ".mysql_error()."<br>"; } //DEBUG
			$images_id = mysql_fetch_array($images_id_sql);
			//echo "cat[".$row['id']."]".$albums[$row['id']]['id']."=".$images_id['id']."; ";
			//if (mysql_error()){  echo "FETCH IMAGES: ".mysql_error()."<br>"; } //DEBUG
			}

			else
					{
					/* Если у нас не альбом, а просто категория - делаем выборку изображений и все */
					$images_id_sql = mysql_query("SELECT id from ibf_gallery_images WHERE category_id = ".$row['id']." AND approved = 1 ORDER BY RAND() LIMIT 1");
					//if (mysql_error()){  echo "SELECT FROM IMAGES: ".mysql_error()."<br>"; } //DEBUG
					$images_id = mysql_fetch_array($images_id_sql);
					}

		$row['last_pic'] = 1; //!!!!!!!!!! Без понятия, почему, но только так все работает... !!!!!!!!!!

		if( $check_permissions && ! $this->ipsclass->check_perms( $row['perms_thumbs'] ) )
		{
				$this->allowed_cats--;
				continue;
		}

					$this->image_ids[ $row['id'] ] = $images_id['id'];   //$row['last_pic'];

		if( $this->restrict && !$row['album_mode'] )
		{
				$this->allowed_cats--;
				continue;
		}

		if (!isset($this->data[ $row['id'] ])) 
		{

			$this->data[ $row['id'] ] = $row;
		}
		else 
		{
			$this->data[ $row['id'] ] = array_merge($this->data[ $row['id'] ],$row);				
		}

					if( $parent == $row['parent'] )
					{
							$this->ordered[] =& $this->data[ $row['id'] ];
					}

		// Let the parent category know they have a child
		$this->data[ $row['parent'] ]['child'][] = $row['id'];

		// Add our own id & our descendants to all the categories above
		if (isset($this->data[ $row['id'] ]['descendants']))
		{
			$to_add = array_merge($this->data[ $row['id'] ]['descendants'], array($row['id']));
		}
		else
		{
			$to_add = array($row['id']);
		}

		$this->_set_decendants($row['parent'],$to_add);
	}
			$this->data[0]['name'] = $root;
}

 

Мозг съеден. Подскажите, что делает строчка, отмеченная восклицательными знаками? Я искал-искал, но так и не нашел, где она может себя проявить, ведь от $row['last_pic'] я вроде как избавился...

Ссылка на комментарий
Поделиться на других сайтах

Присоединиться к обсуждению

Вы можете ответить сейчас, а зарегистрироваться позже. Если у вас уже есть аккаунт, войдите, чтобы ответить от своего имени.

Гость
Ответить в этой теме...

×   Вы вставили отформатированный текст.   Удалить форматирование

  Допустимо не более 75 смайлов.

×   Ваша ссылка была автоматически заменена на медиа-контент.   Отображать как ссылку

×   Ваши публикации восстановлены.   Очистить редактор

×   Вы не можете вставить изображения напрямую. Загрузите или вставьте изображения по ссылке.

Зарузка...
×
 Поделиться
Перейти к списку тем
×
×
  • Создать...

Важная информация

Находясь на нашем сайте, вы соглашаетесь на использование файлов cookie, а также с нашим положением о конфиденциальности Политика конфиденциальности и пользовательским соглашением Условия использования.

  Согласен