Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Запрос объеденяющий 3 таблицы MySQL

Автор: gribikc 18.9.2012, 15:43

Добрый день.

пытаюсь создать каталог с фильтром на подобие того который на сайте http://www.citilink.ru/catalog/parts/cpu/.

Сделал 3 таблицы

Код


--
-- Структура таблицы `filter`
--

CREATE TABLE IF NOT EXISTS `filter` (
  `id_fil` int(11) NOT NULL auto_increment,
  `id_cat` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `type` int(11) NOT NULL,
  PRIMARY KEY  (`id_fil`),
  KEY `id_cat` (`id_cat`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

--
-- Структура таблицы `params`
--

CREATE TABLE IF NOT EXISTS `params` (
  `id_par` int(11) NOT NULL auto_increment,
  `id_tov` int(11) NOT NULL,
  `id_fil` int(11) NOT NULL,
  `i_val` int(9) NOT NULL,
  `c_val` varchar(255) NOT NULL,
  `f_val` double(10,3) NOT NULL,
  PRIMARY KEY  (`id_par`),
  KEY `id_tov` (`id_tov`),
  KEY `f_val` (`f_val`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Структура таблицы `tovar`
--

CREATE TABLE IF NOT EXISTS `tovar` (
  `id_tov` int(11) NOT NULL auto_increment,
  `id_cat` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` float(5,2) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id_tov`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

в таблице `filter` содержатся названия фильтров
в таблице `params` содержатся параметры товаров и соответсвующего фильтра

Помогите оптимизировать запрос всё что я смог придумать:

Код


SELECT * FROM tovar
LEFT JOIN params ON tovar.id_tov=params.id_tov
LEFT JOIN filter ON params.id_fil=filter.id_fil
WHERE  tovar.id_tov IN (
    SELECT params.id_tov FROM params
    WHERE (
        (params.c_val='Белое' AND params.id_fil=1) OR
        (params.c_val='Красное' AND params.id_fil=1)
    )
) AND tovar.id_tov IN (
    SELECT params.id_tov FROM params
    WHERE (
        (params.f_val='0.7' AND params.id_fil=7) OR
        (params.f_val='0.6' AND params.id_fil=7) OR 1=2
    )
)

Автор: Sanchezzz 18.9.2012, 21:14

Код


SELECT * 
FROM tovar
INNER JOIN params ON tovar.id_tov = params.id_tov
INNER JOIN filter ON params.id_fil = filter.id_fil
WHERE (params.c_val =  'Белое' AND params.id_fil =1 ) OR (params.c_val =  'Красное' AND params.id_fil =1)
AND (params.f_val =  '0.7' AND params.id_fil =7) OR (params.f_val =  '0.6' AND params.id_fil =7)

Автор: gribikc 18.9.2012, 21:43

нет не совсем
мой запрос выводит

Код


id_tov    id_cat    name            price        description        id_par    id_tov    id_fil    i_val        c_val        f_va        l    id_fil        id_cat    description    type
2        12        Сахара            576.00    элитное вино        2        2             1          0        Красное        0.000          1             12        Цвет               2
2        12        Сахара            576.00    элитное вино        6        2             7          0        0.7                0.700          7             12        Объём            3
7        12        Либерфраумилк    230.79                          11        7             7          0        0.600           0.600           7             12        Объём            3
7        12        Либерфраумилк    230.79                          10        7             1          0        Белое          0.000           1             12        Цвет               2

а этот

Код


id_tov    id_cat    name            price        description            id_par    id_tov    id_fil    i_val        c_val    f_val        id_fil        id_cat    description    type
1        12        Сакура            12.73    просто вино обычное         1        1        1              0        Белое     0.000       1             12        Цвет            2
7        12        Либерфраумилк    230.79                                 11        7        7              0        0.600      0.600       7            12        Объём        3
7        12        Либерфраумилк    230.79                                 10        7        1              0        Белое     0.000       1            12        Цвет            2

у сакуры объём 0.9

Автор: Zloxa 19.9.2012, 09:59

Цитата(gribikc @ 18.9.2012, 16:43

)

всё что я смог придумать:

Что вас не устраивает в том, что вы придумали?

Добавлено через 3 минуты и 30 секунд

Цитата(gribikc @ 18.9.2012, 16:43

)

оптимизировать запрос

индекс по паре params(f_val,id_fil) - единственное, что приходит в голову

Автор: gribikc 19.9.2012, 12:37

Цитата(Zloxa @ 19.9.2012, 01:59)

Что вас не устраивает в том, что вы придумали?

то что для каждому фильтру надо будет добавлять следующие

Код


AND tovar.id_tov IN (
    SELECT params.id_tov FROM params
    WHERE (
        (params.f_val='0.7' AND params.id_fil=7) OR
        (params.f_val='0.6' AND params.id_fil=7) OR 1=2
    )
)

Автор: Zloxa 19.9.2012, 13:51

Цитата(gribikc @ 19.9.2012, 13:37

)

то что для каждому фильтру надо будет добавлять следующие

И чем именно это вас не удовлетворяет?

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

Чем вы можете обосновать необходимость динамического формирования атрибутов? Почему их нельзя разместить непосредственно в столбцах справочника товара? Полагаю ответ прост. Либо вы(возможно как и ваш заказчик) не знаете каков набор атрибутов будет определен для товара, когда продукт поступит в промышленную эксплуатацию. Либо готовите универсальное, гибкое решение на все случаи жизни. В первом случае это недостаток аналитической проработки, во втором случае - утопия. В обоих случаях - попытка сэкномить. А не рациональность - цена этой экономии. smile

Автор: gribikc 20.9.2012, 08:57

Не удовлетворяет скорость работы этого дела.

Да действительно сначало была определена структура.

Ну а по поводу второго, да действительно делалось универсальное решение

Автор: Zloxa 20.9.2012, 09:16

Цитата(gribikc @ 20.9.2012, 09:57

)

Не удовлетворяет скорость работы этого дела.

Придумайте как сделать так, чтобы по IN отбирался наиболее селективный предикат - позволяющий отсечь наибольшее количество значений из справочника товаров. Фильтр по прочим предикатам перепешите на exists. Ну и, соответственно, добейтесь чтобы везде использовался индексный доступ. Ну и ранее озвученный индекс по паре, тоже своих пару копеек внесет.
Т.е. если у нас наиболее селективный предикат цвет вина (что, всамделе нифига не так), то запрос будет выглядить как-то так:

Код


WHERE  tovar.id_tov IN (
    SELECT params.id_tov FROM params
    WHERE (
        (params.c_val='Белое' AND params.id_fil=1) OR
        (params.c_val='Красное' AND params.id_fil=1)
    )
) 
AND 
exists (select null from params p where p id_tov = t.id_tov and p.id_fil=7 and p.f_val in ('0.6','0.7'))

Цитата(gribikc @ 20.9.2012, 09:57

)

универсальное решение

Гибкость в велосипедостроении - зло. Единственный компонент в велосипеде, который действительно должен быть гибким - троссик. И то, если бюджет позволяет, лучше вместо троссиков использовать гидравлику. smile

Добавлено через 5 минут и 45 секунд

Цитата(Zloxa @ 20.9.2012, 10:16

)

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

Нет, для такого запроса будет лучше индекс по тройке (id_fil,f_val,id_tov). Порядок перечисления полей - значим

Автор: Zloxa 20.9.2012, 09:31

Цитата(Zloxa @ 20.9.2012, 10:16

)

Нет, для такого запроса будет лучше индекс по тройке (id_fil,f_val,id_tov). Порядок перечисления полей - значим

Если требуется осущестлять поиск по диапазону id_fil (использовать для отбора операторы >,<, between), придется строить два индекса. Один для in - по паре (c_val,id_fil), один для exits по тройке (id_to,c_val,id_fil)

Автор: gribikc 1.12.2012, 22:27

нашел решение

Код


SELECT *,COUNT(*) AS c FROM `tovar` 
LEFT JOIN `equivalence` ON `tovar`.`id_tov`=`equivalence`.`id_tov`
WHERE `equivalence`.`id_par` IN (1,6)
GROUP BY `tovar`.`id_tov`
HAVING c=2
ORDER BY  `tovar`.`price`
LIMIT 0,3000

с небольшой переделкой базы но суть таже