Поиск по списку объектов с настраиваемым набором свойств - Базы данных

Ситуация:

Таблицы:
Объекты
Свойства
Справочник значений свойств
Значения свойств объектов

Нужен полноценный поиск по любому набору полей типа
Россия, 27-32 года, без детей, владеет С++ и C#, опыт не менее 2-х лет.

Сейчас я это реализовал, но как-то не слишком здорово:

| users | CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`id_usertype` int(11) NOT NULL default '3',
`login` varchar(255) NOT NULL default 'anonym',
`pw` varchar(255) NOT NULL default 'anonym',
`email` varchar(255) default NULL,
`_order` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |


| profilefields | CREATE TABLE `profilefields` (
`id` int(11) NOT NULL auto_increment,
`id_message` int(11) NOT NULL default '0',
`type` varchar(25) NOT NULL default 'input',
`id_group` int(11) NOT NULL default '0',
`_order` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `i_message` (`id_message`),
KEY `i_group` (`id_group`),
KEY `i_order` (`_order`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

| profiledata | CREATE TABLE `profiledata` (
`id` int(11) NOT NULL auto_increment,
`id_user` int(11) NOT NULL default '0',
`fieldname` varchar(255) default NULL,
`value` text,
`thetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

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

select distinct u.id from ((profiledata as pd left join profilefields as pf on concat("f-",pf.id)=pd.fieldname) inner join users as u on u.id=pd.id_user) where (pd.fieldname="f-6" and pd.value > "10") and (pd.fieldname="f-6" and pd.value < "15")

чтобы искать по двум полям: страна и возраст — придется ДВАЖДЫ джоинить на таблицу значений полей

select distinct u.id from
((((users as u left join photos as p on u.id=p.id_user ) 
) 
inner join (profiledata as pd1
inner join profilefields as pf1 on pd1.fieldname = concat("f-",pf1.id) and pf1.id=6) on pd1.id_user=u.id ) 
inner join (profiledata as pd2 
inner join profilefields as pf2 on concat"f-",pf2.id)=pd2.fieldname and pf2.id=13) on pd2.id_user=u.id)

а вот код
не слишком отточено, но идея тут

// Класс "Запрос"
class request
{
var $units;

function request()
{
$this->units = array();
}

// порождает sql-запрос типа Select на выборку
function render()
{
global $global;
$table = ' '.$global['tables']['users'].' as u ';
$res = '';
foreach ($this->units as $num=>$unit)
{
$fragment = $unit->render($num);
$glue = ($res) ? ' and ' : '';
$res .= ($fragment ? $glue.$fragment : '');
$table = '('.$table.' inner join ('.$global['tables']['profiledata'].' as pd'.$num.' inner join '.$global['tables']['profilefields'].' as pf'.$num.' on concat("f-",pf'.$num.'.id)=pd'.$num.'.fieldname) on u.id=pd'.$num.'.id_user)';
}
$res = 'select distinct u.id from '.$table.($res ? (' where '.$res) : '');

// убираем парные пробелы
while (strpos($res,' ')!=false) $res = str_replace(' ',' ',$res);
return $res;
}

}

// Класс "запросная единица "
class unit
{
var $field;
var $constraint;

function unit($field,$constraint)
{
$this->field = $field;
$this->constraint = $constraint;
}

function render($num)
{
$res = '';
if ( ($this->field) && ($this->field->name) && ($this->constraint) && 
(($this->constraint->value>'') || ($this->constraint->min>'') 
|| ($this->constraint->max>''))
)
{
// Есть что рендерить
$comma = ($this->field->isstring) ? '"' : '';
// значение
if ($this->constraint->type=='value')
{
// "=" или "like"
$sign = ($this->constraint->comparison == 'strong') ? ' = ' : 
(($this->constraint->comparison == 'like') ? ' like ' : '');
if ($sign)
{
if ($this->field->virtual)
$res .= ' (pd'.$num.'.fieldname="'.$this->field->name.'" and pd'.$num.'.value'.$sign.' '.
$comma.$this->constraint->value.$comma.')';
else
$res .= ' (`'.$this->field->name.'` '.$sign.' '.
$comma.$this->constraint->value.$comma.')';
}
}
// интервал
if ($this->constraint->type=='interval')
{
// левая граница
if ($this->constraint->min>'')
{
// ">" или ">="
$sign = ($this->constraint->comparison == 'strong') ? ' > ' : 
(($this->constraint->comparison == 'not strong') ? ' >= ' : '');
if ($sign)
{
if ($this->field->virtual)
$res .= ' (pd'.$num.'.fieldname="'.$this->field->name.'" and pd'.$num.'.value'.$sign.' '.
$comma.$this->constraint->min.$comma.')';
else
$res .= ' (`'.$this->field->name.'` '.$sign.' '.
$comma.$this->constraint->min.$comma.')';
}
}
// " and "
if ( ($this->constraint->max>'') && ($this->constraint->min>'') ) $res .= ' and ';
// правая граница
if ($this->constraint->max>'')
{
// "<" или "<="
$sign = ($this->constraint->comparison == 'strong') ? ' < ' : 
(($this->constraint->comparison == 'not strong') ? ' <= ' : '');
if ($sign)
{
if ($this->field->virtual)
$res .= ' (pd'.$num.'.fieldname="'.$this->field->name.'" and pd'.$num.'.value'.$sign.' '.
$comma.$this->constraint->max.$comma.')'; 
else
$res .= ' (`'.$this->field->name.'` '.$sign.' '.
$comma.$this->constraint->max.$comma.')';
}
}
}
} 
return $res;
}
}

class field
{
var $isstring;
var $name;
var $caption;
var $virtual; // =1 if this field was added in editor and is not physically exists in `users` table

function field($name, $isstring='1', $virtual='0')
{
$this->name = $name;
$this->isstring = $isstring;
$this->virtual = $virtual;
}
}

// ограничение. 
class constraint
{
var $value;
var $min;
var $max;
// 'strong' => '<'
// 'not strong' =? '<='
// 'like' =? 'like'
var $comparison;
// тип: value или interval
var $type;

// Есть два варианта вызова конструктора
// при type=value передают одно значение
// при type=interval передают два значения: меньшее, потом большее
function constraint($type,$value1,$value2 = '',$comparison = 'strong')
{
$this->type = $type;
$this->comparison = $comparison;
if ($this->type=='value')
{
$this->value = $value1;
}
if ($this->type=='interval')
{
$this->min = $value1;
$this->max = $value2;
}
}

}

Теперь собственно вопрос. Это разрабатывается для очень посещаеемого сайта, где поиском буду пользоваться часто.
Будет примерно 200-500 полей профиля, десятки тысяч пользователей, тысячи значений справочников, частота поиска — 1 запрос в секунду.

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

Все join — inner, все индексы — присутствуют, все, что можно — будет кешироваться.
Обновление профиля происходит на два порядка реже, чем поиск по нему

Здравствуйте, StephenVLG, Вы писали:

SVL>Ситуация:

SVL>Таблицы:
SVL>Объекты
SVL>Свойства
SVL>Справочник значений свойств
SVL>Значения свойств объектов

SVL>Нужен полноценный поиск по любому набору полей типа
SVL>Россия, 27-32 года, без детей, владеет С++ и C#, опыт не менее 2-х лет.

SVL>Сейчас я это реализовал, но как-то не слишком здорово:

Почитай тред Сложный запрос на простой таблице.
У тебя похожая ситуация. Только хуже

Я читал.
1. Возможно ли улучшить текущую реализацию с сохранением структуры БД
2. Чем грозит работа с такой структурой при серьезной загрузке?

Здравствуйте, StephenVLG, Вы писали:

Вариант с избавлением от многих join'ов:

CREATE TABLE [dbo].[FieldData](
    [ItemId] [int] NOT NULL,
    [FieldID] [int] NOT NULL,
    [FieldValue] [varchar](100) NOT NULL,
 CONSTRAINT [PK_FieldData] PRIMARY KEY CLUSTERED 
(
    [ItemId] ASC,
    [FieldID] ASC
)
)

CREATE TABLE [dbo].[FieldQuery](
    [QueryID] [int] NOT NULL,
    [FieldID] [int] NOT NULL,
    [FieldValue] [varchar](100) NOT NULL,
 CONSTRAINT [PK_FieldQuery] PRIMARY KEY CLUSTERED 
(
    [QueryID] ASC,
    [FieldID] ASC
)
)

Запрос (критерии добавляются в табличку FieldQuery):

select 
        top 500
    d.ItemId
from
    FieldData d
    join FieldQuery q on q.fieldid = d.fieldid and d.fieldvalue = q.fieldvalue
Where
        q.QueryId = 1
group by 
        d.ItemId
having 
        count(*) = (select count(*) from FieldQuery where QueryId = 1)

1. Как это будет работать на больших объемах -- не знаю, надо проверять и играть с индексами (в т.ч. clustered/non clustered).

2. Работает как условие И

3. Проверяет четкое сравнение

На больших объемах данных и большом количестве полей для поиска, join может оказаться лучше, чем вариант c OR предложенный MatFiz здесь. Надо проверять...

Здравствуйте, Андрей Никифоров, Вы писали:

АН>Здравствуйте, StephenVLG, Вы писали:

АН>Вариант с избавлением от многих join'ов:

АН>Запрос (критерии добавляются в табличку FieldQuery):

АН>

АН>select 
АН>        top 500
АН>    d.ItemId
АН>from
АН>    FieldData d
АН>    join FieldQuery q on q.fieldid = d.fieldid and d.fieldvalue = q.fieldvalue
АН>Where
АН>        q.QueryId = 1
АН>group by 
АН>        d.ItemId
АН>having 
АН>        count(*) = (select count(*) from FieldQuery where QueryId = 1)
АН>

АН>. Надо проверять...

спасибо за идею, очень красиво. минус в том, что не видно способа выполнять сравнение значений (<>) при поиске — а это часто важно

сейчас на БД в 8000 пользователей поиск менее чем по 10 полям выполняется до секунды (при работе 2-5 пользователей одновременно). Больших тормозов не замечено

Здравствуйте, StephenVLG, Вы писали:

АН>>Вариант с избавлением от многих join'ов:
...
АН>>Запрос (критерии добавляются в табличку FieldQuery):
...
АН>>. Надо проверять...

SVL>спасибо за идею, очень красиво. минус в том, что не видно способа выполнять сравнение значений (<>) при поиске — а это часто важно

SVL>сейчас на БД в 8000 пользователей поиск менее чем по 10 полям выполняется до секунды (при работе 2-5 пользователей одновременно). Больших тормозов не замечено

я не знаю, что за база...

пример из oracle. там есть snapshot (MATERIALIZED VIEW).

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

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

плюсы и минусы решения можешь прикинуть сам.

во

B>я не знаю, что за база...

60 полей профиля, 15000 пользователей, выходит около миллиона записей о значениях полей. поиск очень радует скоростью — до 10 полей в запросе — до секунды ищет

	От:	StephenVLG
	Дата:	06.03.06 10:48
	Оценка:

	От:	MatFiz
	Дата:	06.03.06 13:11
	Оценка:

	От:	StephenVLG
	Дата:	06.03.06 15:44
	Оценка:

	От:	Андрей Никифоров
	Дата:	07.03.06 19:19
	Оценка:

	От:	StephenVLG
	Дата:	14.03.06 05:51
	Оценка:

От:	bastrakov	http://bastrakof.livejournal.com/
Дата:	14.03.06 08:33
Оценка:

	От:	Аноним
	Дата:	21.03.06 09:36
	Оценка: