Нестандартный тип хранения данных и генерация запросов

[Alkeeper]

Нестандартный тип хранения данных и генерация запросов

Суть проблемы такова:

Есть каталог услуг произвольной структуры, грубо говоря, одна услуга имеет в детализации 5 полей, другая 10, третья еще сколько-то.

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

Пришел к такой структуре БД

Табл. 1 - Типы услуг (общее описание, название типа, отношение к вышестоящим уровням дерева).

serv_id|serv_en|serv_ru|serv_description

Табл. 2 Типы Полей (по serv_id относятся к типам услуг)

var_id|serv_id|var_name|var_name_ru|possible_values|position

Табл. 3. Частные случаи услуг. (Общая информация для любой услуги)

id|serv_id|owner_id|s_name|s_description


Табл. 4 Значения переменных, характерных для конкретных типов услуг

value_id|var_id|s_id|value

Задача - вывести список услуг конкретного типа.
тип передается либо через GET, либо вытаскивается из request_uri при включенном mod_rewrite

Додумался до двух способов извлечения данных:
1. Заключается в следующем.
а) Первым запросом получаем названия переменных и формируем шапку для табличного представления.
б) Вторым запросом получаем общие данные по услугам из таблицы 3.
в) В цикле обращаемся к таблице значений, выдергиваем для каждой строки нужные данные в том же порядке, что и при формировании шапки
г) вложенным циклом добиваем эти значения в массивы к результату из таблицы 3.

Обновленные строки отдаем в шаблонизатор (смарти).

Недостатоки - высокая сложность при сортировке и наложении условий при выборке, большое количество запросов.

2. Состоит в идее 2х запросов.

а) Первый запрос - получение шапки (и латинских псевдонимов переменных)

б) Вторым шагом идет не запрос, а генерация второго запроса на основе результатов первого. т.е.

В цикле формируем составляющие Select, from и where (возмозжно и order by) след образом. при формировании селекта мы несколько раз просим вернуть value из табл. 4 каждый раз присваивая значению и запрашиваемой таблице разные псевдонимы (для этого нужны латинские псевдонимы из первого запроса)

т.е примерно так

select serv.*,var1_t.value as var1, var2_t.value as var2
from servises as serv, values as var1_t, values as var2_t
where var1_t.serv_id = serv.id and var1_t.var_id = '{$var[1]}' and var2_t.serv_id = serv.id and var2_t.var_id = '{$var[2]}' and serv.serv_type_id = '$sid'

массив $var приходит из первого запроса, $sid = определение типа выводимой услуге. через GET

вот пример реально сгенерированного запроса

SELECT
`serv`.*,
`valyuta_t`.`value` AS `valyuta`,
`min_srok_t`.`value` AS `min_srok`,
`maks_srok_t`.`value` AS `maks_srok`,
`min_stavka_t`.`value` AS `min_stavka`,
`maks_stavka_t`.`value` AS `maks_stavka`
FROM
`cat_services` AS `serv`,
`cat_service_values` AS `valyuta_t`,
`cat_service_values` AS `min_srok_t`,
`cat_service_values` AS `maks_srok_t`,
`cat_service_values` AS `min_stavka_t`,
`cat_service_values` AS `maks_stavka_t`
WHERE
`valyuta_t`.`serv_id` = `serv`.`s_id` AND `valyuta_t`.`var_id` = '118' AND
`min_srok_t`.`serv_id` = `serv`.`s_id` AND `min_srok_t`.`var_id` = '119' AND
`maks_srok_t`.`serv_id` = `serv`.`s_id` AND `maks_srok_t`.`var_id` = '120' AND
`min_stavka_t`.`serv_id` = `serv`.`s_id` AND `min_stavka_t`.`var_id` = '121' AND
`maks_stavka_t`.`serv_id` = `serv`.`s_id` AND `maks_stavka_t`.`var_id` = '122' AND
`serv`.`cid` = '3';

По идее если к результату этому запросу правильно сформировать доп. условия where и order by должен бы получиться еще и недурственный простор для экспериментов с поиском.

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

Есть ли какая-то альтернатива? хотя бы направление задайте???

Спасибо, что хватило терпения прочесть.

-~{}~ 28.02.06 11:51:

Странно, что никакой реакции, даже фраз "РТФМ" или "Выражайся яснее" не наблюдается.....

 

[Wicked]

читал по диагонали...

что говорит EXPLAIN ?

 

[Alkeeper]

Да ничего особенного не говорит, Все, говорит, нормально...

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

Возникает резонный вопрос, а для MySQL ли эта задача????

 

[Wicked]

каков был критерий "нормальности" результата EXPLAIN'а ?

 

[Alkeeper]

+--+-----------+-------+-----+-------------+-------+-------+-----+----+-----------+
|id|select_type| table |type |possible_keys|key    |key_len|ref  |rows|Extra      |
+--+-----------+-------+-----+-------------+-------+-------+-----+----+-----------+
|1 |SIMPLE     | serv  |const|PRIMARY      |PRIMARY|4      |const|1   |           |
+--+-----------+-------+-----+-------------+-------+-------+-----+----+-----------+
|1 |SIMPLE     | var1_t|ALL  |NULL         |NULL   |NULL   |NULL |5644|Using where|
+--+-----------+-------+-----+-------------+-------+-------+-----+----+-----------+
|1 |SIMPLE     | var2_t|ALL  |NULL         |NULL   |NULL   |NULL |5644|           |
+--+-----------+-------+-----+-------------+-------+-------+-----+----+-----------+

 

[Wicked]

ну из этого EXPLAIN'а видно, что он пытается перебрать порядка 5644 * 5644 = 31854736 записей Это не совсем нормально...

Я не совсем понимаю, почему там TYPE=ALL, но я бы попробовал для начала сделать такие индексы в таблице values:
idx1 (serv_id, var_id, value)
и
idx2 (var_id, serv_id, value)
и посмотрел бы на explain после этого...

-~{}~ 01.03.06 11:05:

ALL

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

ну да, все сходится

-~{}~ 01.03.06 11:07:

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

 

[Alkeeper]

Да индексирование здорово помогло, спасибо за наводку

 

[Rammstein]

Имхо, такой способ хранения полнейший гемор. Испытано на одном проекте - больше не возвращался.

 

[Alkeeper]

Rammstein, а есть альтернативы?

таблицу в 100-200 полей не предлагать, хранение сериализованных данных тоже...

 

[Wicked]

Alkeeper, и который из индексов теперь показывается в explain ?

 

[Alkeeper]

+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+
|id|select_type|table             |type |possible_keys|key    |key_len|ref               |rows|Extra      |
+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+
|1 |SIMPLE     |serv              |ref  |PRIMARY,cid  |cid    |4      |const             |86  |Using where|
+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+
|1 |SIMPLE     |pervonachalnyyvz_t|ref  |var_id       |var_id |8      |const,db.serv.s_id| 1  |Using where|
+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+
|1 |SIMPLE     |stavkavalyuta-do_t|ref  |var_id       |var_id |8      |const,db.serv.s_id| 1  |Using where|
+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+
|1 |SIMPLE     |stavkavalyuta-ru_t|ref  |var_id       |var_id |8      |const,db.serv.s_id| 1  |Using where|
+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+
|1 |SIMPLE     |maksimalnyysrokp_t|ref  |var_id       |var_id |8      |const,db.serv.s_id| 1  |Using where|
+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+
|1 |SIMPLE     |nalichieporuchit_t|ref  |var_id       |var_id |8      |const,db.serv.s_id| 1  |Using where|
+--+-----------+------------------+-----+-------------+-------+-------+------------------+----+-----------+