Меню
Что нового?

Как вставить в таблицу только уникальные данные с минимальными затратами ресурсов?

Бочонок

Бочонок

http://frontender.info
Как вставить в таблицу только уникальные данные с минимальными затратами ресурсов?

Приятного времени суток.
Появилась необходимость ввести на сайте поддержку тегов.
в таблице пользователей ааа есть набор полей куда они через запятую записываются.

aaa
------
...
music,
games,
films,
...

что бы сделать предложение тегов на сайте в заполняемом поле я создаю вторую таблицу специально для тегов

ббб
------
tag
category
FULLTEXT INDEX tag (tag)
FULLTEXT INDEX category (category)
id

раз в сутки(возможно чаще... еще решили) в таблицу ббб должны будут вноситься те записи, которые еще в нем отсутствуют.

в связи с этим есть ряд вопросов:

1. таблица ббб используется только для того чтобы извлекать теги-подсказки. может быть целесообразнее хранить их как то по другому? не в бд? но как тогда организовать "выборку"?
2. стоит ли приплетать полнотекстовый поиск (по идее он должен быть быстрее) или может быть в данном случае стоит ограничиться like binary %кусок тега%?
3. стоит ли добавлять только новые теги или просто удалять и создавать каждый раз таблицу по новой?
4. если все же обновлять - то как это лучше сделать?
Мне пока кроме как выбрать все записи из таблицы ааа, разделить на теги и проверять по одному наличие в ббб ничего в голову не пришло.

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

Gas

может по одной?
Зачем хранить через запятую, зачем fulltext - не понятно. Чем не угодило хранение всех тегов в отдельной таблице + таблица связей тег_пользователь ?
 

berkut

Новичок
а создать составной индекс вместе с FT нельзя? если 2 поля - category_id INT, description TEXT - и WHERE category_id = 1 AND MATCH()... - только фулскан, без вариантов?
 

kruglov

Новичок
не вникал в структуру таблицы, а для быстрого выяснения, не писали ли мы в базу точно такую же запись, можно добавить поле с каким-нить хешем всех данных. Если хеша новой записи нигде в базе нету, значит новое. Если есть - делаем еще одну проверку на сравнение с данными записей с совпавшим хешем (т.к. хэш - теоретически функция сюръективная и не более).
 
Бочонок

Бочонок

http://frontender.info
Переделал бд

таблица ссылки (ааа)
______________
tag_id
category
element_id
id

таблица с тегами (ббб)
______________
tag
id


в (ааа) tag_id соответствует id в (ббб)
element_id содержит id элемента в таблице описывающей объект к которому тег относится
category содержит слово, которое определяет к объекту какого типа относиться element_id ,тоесть например: 'фильм' или "фотогалеея" или "блог".

2kruglov:
а какая разница - сравнивать строку со строкой или хеш с хешем.
поиск чисел происходит быстрее?

тоесть
PHP: 
SELECT count(*) from ббб where tag='Убить била'
медленее чем
PHP: 
SELECT count(*) from ббб where tag_md5=md5('Убить била')
?


2berkut:
а можно поподробнее?
что за составной индекс?

-~{}~ 23.04.08 21:46:

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

Что вы по этому поводу думаете?
 

Onu

Новичок
Бочонок
PHP: 
  UNIQUE KEY `tag` (`tag`)
*нужно задать при создании таблицы или изменить имеющуюся*
В таблицу не будут попадать строки с уже имеющимся тагом.
 

Gas

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

какая разница - сравнивать строку со строкой или хеш с хешем.
Нажмите для раскрытия...
Разница в размере данных, но в том варианте, который ты привёл - разницы нет. А вот crc32() - 4 байта или CONV(LEFT(MD5('строка'), 16), 16, 10) - 8 байт, только кроме сравнения хеша (по индексу), нужно сравнивать и саму строку из-за возможных коллизий.

Раз в н часов запускаем скрипт, который преобразует это нечто в теги
Нажмите для раскрытия...
почему раз в N часов, а не сразу добавлять тег?
Или уже такая большая база тегов, чтоб добавление нового влечёт сильную нагрузку при перестройке индексов?


но если при каждом изменении тегов мы анализируем все теги (например пользовательские) на совпадение со списком тегов... то это наверное серьезные временные затраты и затраты вычислительных мощностей.
Нажмите для раскрытия...
правильные индексы решат эту проблему.
 
Бочонок

Бочонок

http://frontender.info
тоесть делать
PHP: 
1. 
INSERT IGNORE into ббб(tag) values('тег');
2.
select 
count(*) 
from 
aaa,ббб 
where 
(aaa.element_id='12')&&
(aaa.category='категория')&&
(ббб.tag='тег')&&
(ббб.id=aaa.tag_id)
и если такой нету, то
PHP: 
3.
select id from ббб where tag='тег' limit 1
4.
insert into
aaa(tag_id,category,element_id) 
values(полученый в прошлом запросе id,'категоря',id объекта к которому привязан тег)
Так?
Или можно как то улучшить?
 

Gas

может по одной?
Вполне вариант.
Получается можно 2-ой и 1-ый запросы поменять местами, а 3-ий и 4-ый объеденить в один - insert ... select syntax.
Замечание про вынос категорий в отдельную таблицу остаётся в силе.
 
Бочонок

Бочонок

http://frontender.info
правильные индексы решат эту проблему.
Нажмите для раскрытия...
А можно поподробнее?

-~{}~ 24.04.08 03:09:

Замечание про вынос категорий в отдельную таблицу остаётся в силе.
Нажмите для раскрытия...
Их будет всего 4-5. не больше.
но если добавить, то придется каждый раз еще и получать id нужной категории.
Это правда имеет смысл?
 

Gas

может по одной?
А можно поподробнее?
Нажмите для раскрытия...
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
http://dev.mysql.com/doc/refman/5.0/en/query-speed.html

Это правда имеет смысл?
Нажмите для раскрытия...
Имеет смысл - меньше размер таблицы, меньше размер индексов, быстрее запросы (чем болье таблица, тем выгода сильнее). Cделай категории или enum полем, или вынеси их на уровень приложения -"видео"=>1, "музыка"=>2, а в базе поле tinyint
 
Бочонок

Бочонок

http://frontender.info
Так.
Переписал вроде бы.

PHP: 
	create table tag_link(
		tag_id int,
		element_id int,
		tag_category_id int,
		id int auto_increment primary key
	) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

	create table tag_category(
		tag_category varchar(255) not null unique key,
		id int auto_increment primary key
	) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

	create table tag(
		tag varchar(255) not null unique key,
		id int auto_increment primary key
	) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
И написал класс по работе с этим безобразием.
Посмотрите пожалуста - може можно его как то ускорить?

PHP: 
class tag_operator{
	/*Добавляет массив тегов определенного объекта определенной категории*/
	public function add_tag_set($element_id,$category_name,$tag_set){
		if(!is_array($tag_set)){
			if(is_string($tag_set)){
				if(strpos($tag_set,',')===false){
					$tag_set = array($tag_set);
				}else{
					$tag_set = explode(',',trim($tag_set));
					$tag_set = array_unique($tag_set);
				}
			}else{
				return false;	
			}
		}
		$this->delete_all_tags($element_id,$category_name);
		foreach ($tag_set as $key => $value){
			$this->add_tag($element_id,$category_name,$value);
		}
		return true;
	}
	/*Добавляет тег определенного объекта определенной категории*/
	public function add_tag($element_id,$category_name,$tag){
		$query="
			insert ignore
			into
				tag(tag)
				values('".addslashes(trim($tag))."');			
		";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}	
		$query="
			insert into
				tag_link(tag_id,element_id,tag_category_id)
				values(last_insert_id(),
".(int)$element_id.",
(select id from tag_category where tag_category='".addslashes($category_name)."' limit 1))
		";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}
	}
	/*Удаляет все теги определенного объекта определенной категории*/
	public function delete_all_tags($element_id,$category_name){
		$query="
			delete
			from 
				tag_link
			where
				(tag_link.tag_category_id=
(select id from tag_category where tag_category='".addslashes($category_name)."' limit 1))&&
				(tag_link.element_id=".addslashes($element_id).")
		";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}
	}
	/*Проверяет есть ли у данного объекта с данным тегом в данной категории*/
	public function is_tag_link_set($element_id,$category_name,$tag){
		$query="
			select 
				count(*) as tag_is_set
			from 
				tag,tag_link
			where
				(tag.tag='".addslashes($tag)."')&&
				(tag_link.tag_id=tag.id)&&
				(tag_link.tag_category_id=
(select id from tag_category where tag_category='".addslashes($category_name)."' limit 1))&&
				(tag_link.element_id=".addslashes($element_id).")
			";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}
		$row=mysql_fetch_assoc($result);
		if((int)$row['tag_is_set']==1){
			return true;
		}else{
			return false;	
		}
	}
	/*Удаляет тег определенного объекта определенной категории*/
	public function delete_tag($element_id,$category_name,$tag){
		if($this->is_tag_link_set($element_id,$category_name,$tag)){
			$query="
				delete
				from 
					tag_link
				where
					(tag_link.tag_id=(select id from tag where tag='".addslashes($tag)."' limit 1))&&
					(tag_link.tag_category_id=
(select id from tag_category where tag_category='".addslashes($category_name)."' limit 1))&&
					(tag_link.element_id=".addslashes($element_id).")
				limit 1
				";
			if(!$result=mysql_query($query)){
				throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
			}
			return true;
		}else{
			return false;
		}	
	}
	/*Добавляет категорию*/
	public function add_tag_category($name){
		$query="
			insert
			into
				tag_category(tag_category)
				values('".addslashes($name)."');			
		";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}	
	}
	/*Удаляет категорию*/
	public function remove_tag_category($name){
		$query="
			delete
			from
				tag_category
			where
				tag_category='".addslashes($name)."'
			limit 1
		";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}	
	}
	/*Получение списка тегов для объекта по категории*/
	public function get_tag_list($element_id,$category_name){
		$query="
			select 
				tag.tag as tag
			from 
				tag,tag_link
			where
				(tag_link.tag_id=tag.id)&&
				(tag_link.tag_category_id=
(select id from tag_category where tag_category='".addslashes($category_name)."' limit 1))&&
				(tag_link.element_id=".addslashes($element_id).")
			order by 
				tag
			";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}
		$tag_list = array();
		while($row=mysql_fetch_assoc($result)){
			$tag_list[]	= $row['tag'];
		}
		return $tag_list;
	}
	/*Получение списка тегов по ключу*/
	public function get_tag_like($tag_part,$num=5){
		$query="
			select 
				tag
			from 
				tag
			where
				tag like '%".addslashes($tag_part)."%'
			order by 
				rand()
			limit
				".(int)$num."
			";
		if(!$result=mysql_query($query)){
			throw new Exception("Запрос: ".$query." Ошибка: ".mysql_error());
		}
		$tag_list = array();
		while($row=mysql_fetch_assoc($result)){
			$tag_list[]	= $row['tag'];
		}
		return $tag_list;
	}
}
-~{}~ 24.04.08 11:05:

2Gas:
Спасибо сейчас почитаю.
 
Войдите или зарегистрируйтесь для ответа.
Сверху