ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько Join таблицы саму на себя
25 сообщений из 44, страница 1 из 2
  1  все
Несколько Join таблицы саму на себя
    #40004468
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
Добрый день, существует 3 таблицы. Таблица product

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE TABLE `product` (
 `product_id` int(11) NOT NULL AUTO_INCREMENT,
 `model` varchar(64) NOT NULL,
 `quantity` int(4) NOT NULL DEFAULT '0',
 `image` varchar(255) DEFAULT NULL,
 `manufacturer_id` int(11) NOT NULL,
 `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
 `date_available` date NOT NULL DEFAULT '1990-09-02',
 `viewed` int(5) NOT NULL DEFAULT '0',
 `date_added` datetime NOT NULL,
 `date_modified` datetime NOT NULL,
 PRIMARY KEY (`product_id`),
 KEY `date_modified` (`date_modified`),
 KEY `quantity_image` (`quantity`,`image`),
 KEY `model` (`model`),
 KEY `manufacturer_id` (`manufacturer_id`),
 KEY `price` (`price`)
) ENGINE=MyISAM AUTO_INCREMENT=125826 DEFAULT CHARSET=utf8



Таблица attribute

Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE `attribute` (
 `attribute_id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL,
 `value` varchar(32) NOT NULL,
 PRIMARY KEY (`attribute_id`),
 KEY `attribute_id_name_value` (`attribute_id`,`name`,`value`)
) ENGINE=MyISAM AUTO_INCREMENT=657 DEFAULT CHARSET=utf8



Таблица product_to_attribute

Код: sql
1.
2.
3.
4.
5.
CREATE TABLE `product_attribute` (
 `product_id` int(11) NOT NULL,
 `attribute_id` int(11) NOT NULL,
 PRIMARY KEY (`product_id`,`attribute_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8



В реальности имеем товары, которые содержать несколько атрибутов, 1 и больше.

Необходимо получать товары по заданным атрибутам. В качестве реализации использую вот такой запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
SELECT p.model,
         p.product_id,
            
FROM product p
INNER JOIN product_attribute AS pa1
    ON (p.product_id=pa1.product_id)
INNER JOIN product_attribute AS pa2
    ON (p.product_id=pa2.product_id)
INNER JOIN product_attribute AS pa3
    ON (p.product_id=pa3.product_id)
INNER JOIN product_attribute AS pa4
    ON (p.product_id=pa4.product_id)
INNER JOIN product_attribute AS pa5
    ON (p.product_id=pa5.product_id)
INNER JOIN product_attribute AS pa6
    ON (p.product_id=pa6.product_id)
INNER JOIN product_attribute AS pa7
    ON (p.product_id=pa7.product_id)
INNER JOIN product_attribute AS pa8
    ON (p.product_id=pa8.product_id)
WHERE p.quantity > 0
        AND pa1.attribute_id IN (12, 23)
        AND pa2.attribute_id IN (2, 37, 42, 54, 1)
        AND pa3.attribute_id IN (4, 38)
        AND pa4.attribute_id IN (535)
        AND pa5.attribute_id IN (7, 13)
        AND pa6.attribute_id IN (418, 368, 237)
        AND pa7.attribute_id IN (8, 14, 41)
        AND pa8.attribute_id IN (24, 22, 2) AND p2c.category_id IN (15, 5) AND p.manufacturer_id IN (197, 62, 4) AND p.price BETWEEN 32317 AND 1000000 GROUP BY p.model LIMIT 0,60



Если атрибутов до 5, то скорость выполнения приемлема, если 8 и больше, то скорость > 20 cек.

В качестве СУБД использую MySQL 5.7.31.

Записей в product > 150 000, attribute 600, product_to_attribute > 500 000 и база будет расти.

Не могли бы вы подсказать или натолкнуть как правильно оптимизировать скорость выполнения запросов?
...
Рейтинг: 0 / 0
01.10.2020, 10:56
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004473
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
para_bit,
1. Перевести все в InnoDb и даже лучше в xtraDb или как там оно зовется.
2. Навешать правильные ключи.
3. Объединять 1 раз с group by и having с подсчетом кол-ва атрибутов у товара .. в общем, перейти на eav выборки.
как-то так. Детальней надо вспоминать .. давно с продольным хранением атрибутов на якшался.. подзабыл ужо.

Кстати! Может коллективно вспомнить, обсудить и выложить в фак? Типо "штатная работа с EAV" .. там при правильной готовке запросов снижение скорости от прямых выборок из широких таблиц не превышало 2-3 раз на MySql 5.1.47 .. когда это было .. ;)
...
Рейтинг: 0 / 0
01.10.2020, 11:15
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004490
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
Arhat109,
Спасибо за ваш ответ,

1) сейчас работает на MyIsam, насколько я понимаю выборка с этим типом шустрее

2) Индексы и ключе расставлены
...
Рейтинг: 0 / 0
01.10.2020, 11:50
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004498
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
para_bit,
В таком разе, что Вы хатите? Раз "все есть и шустрее" .. ;)
...
Рейтинг: 0 / 0
01.10.2020, 12:08
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004501
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
Arhat109,
где-то тут, за 2011-2013гг болтается описание того сервака, на котором всё это отрабатывалось, кажется 2 ядра Xeon по 1.9Мгц и 8 гигабайт оперативы. СУБД товаров - 500тыс строк. Атрибутов около 3-15 на товар. Средняя выборка по атрибутам в пределах 0.6сек.
как-то так, возможно micsoft ещё помнит.. ;)
...
Рейтинг: 0 / 0
01.10.2020, 12:10
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004599
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
para_bit

Необходимо получать товары по заданным атрибутам. В качестве реализации использую вот такой запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
SELECT p.model,
         p.product_id,
            
FROM product p
INNER JOIN product_attribute AS pa1
    ON (p.product_id=pa1.product_id)
INNER JOIN product_attribute AS pa2
    ON (p.product_id=pa2.product_id)
INNER JOIN product_attribute AS pa3
    ON (p.product_id=pa3.product_id)
INNER JOIN product_attribute AS pa4
    ON (p.product_id=pa4.product_id)
INNER JOIN product_attribute AS pa5
    ON (p.product_id=pa5.product_id)
INNER JOIN product_attribute AS pa6
    ON (p.product_id=pa6.product_id)
INNER JOIN product_attribute AS pa7
    ON (p.product_id=pa7.product_id)
INNER JOIN product_attribute AS pa8
    ON (p.product_id=pa8.product_id)
WHERE p.quantity > 0
        AND pa1.attribute_id IN (12, 23)
        AND pa2.attribute_id IN (2, 37, 42, 54, 1)
        AND pa3.attribute_id IN (4, 38)
        AND pa4.attribute_id IN (535)
        AND pa5.attribute_id IN (7, 13)
        AND pa6.attribute_id IN (418, 368, 237)
        AND pa7.attribute_id IN (8, 14, 41)
        AND pa8.attribute_id IN (24, 22, 2) AND p2c.category_id IN (15, 5) AND p.manufacturer_id IN (197, 62, 4) AND p.price BETWEEN 32317 AND 1000000 GROUP BY p.model LIMIT 0,60



ЯНХНП. Запрос кривой. Запятая перед FROM, неизвестная таблица p2c, куча непонятных жойнов, в списке выбора поля, не участвующие в группировке.
...
Рейтинг: 0 / 0
01.10.2020, 16:44
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004635
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
paver,
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
SELECT p.model,
         p.product_id            
FROM product p
INNER JOIN product_attribute AS pa1
    ON (p.product_id=pa1.product_id)
INNER JOIN product_attribute AS pa2
    ON (p.product_id=pa2.product_id)
INNER JOIN product_attribute AS pa3
    ON (p.product_id=pa3.product_id)
INNER JOIN product_attribute AS pa4
    ON (p.product_id=pa4.product_id)
INNER JOIN product_attribute AS pa5
    ON (p.product_id=pa5.product_id)
INNER JOIN product_attribute AS pa6
    ON (p.product_id=pa6.product_id)
INNER JOIN product_attribute AS pa7
    ON (p.product_id=pa7.product_id)
INNER JOIN product_attribute AS pa8
    ON (p.product_id=pa8.product_id)
WHERE p.quantity > 0
        AND pa1.attribute_id IN (12, 23)
        AND pa2.attribute_id IN (2, 37, 42, 54, 1)
        AND pa3.attribute_id IN (4, 38)
        AND pa4.attribute_id IN (535)
        AND pa5.attribute_id IN (7, 13)
        AND pa6.attribute_id IN (418, 368, 237)
        AND pa7.attribute_id IN (8, 14, 41)
        AND pa8.attribute_id IN (24, 22, 2) AND p.manufacturer_id IN (197, 62, 4) AND p.price BETWEEN 32317 AND 1000000 GROUP BY p.model LIMIT 0,60



спасибо, поправил запрос в этом сообщении, каждый JOIN это поиск по конкретному типу атрибута, 8 Join = 8 типов атрибутов. Про какие вы поля, какие не участвуют в группировке
...
Рейтинг: 0 / 0
01.10.2020, 17:45
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004708
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
para_bit
Код: sql
1.
AND pa2.attribute_id IN (2, 37, 42, 54, 1)

para_bit
Код: sql
1.
AND pa8.attribute_id IN (24, 22, 2)

А почему одинаковые числа (2) в разных атриубтах?
...
Рейтинг: 0 / 0
01.10.2020, 23:02
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004711
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
Да, мне тоже непонятно.

Если это восемь разных групп атрибутов, то атрибут не должен попадаться в разных группах, верно?

Иначе это похоже на

" синий или красный" +
"мелкий или средний" +
"гладкий или синий "
...
Рейтинг: 0 / 0
01.10.2020, 23:37
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004715
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
По-моему, эту задачу можно решить одним джойном, если выполняются следующие условия:
- все атрибуты делятся на известное число категорий (цвет, размер, производитель)
- внутри категории, атрибуты взаимоисключающие (товар не может быть зеленый и желтый одновременно)
- из первых двух следует, что у товара не может быть больше атрибутов, чем категорий
это также означает, что атрибуты для критерия поиска можно смешать вместе (ищем красный + маленький + китайский)

Ниже я привожу пример (правда на Оракле, я с ним работаю),
а тут попробую написать сохраняя синтакс ТС

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT p.model,
         p.product_id            
FROM product p
INNER JOIN (SELECT pa.product_id, 
                   count(distinct pa.attribute_id) 
              from product_attribute pa
             where pa.attribute_id in (12, 23, 2, 37, 42, 54, 1,4, 38,535,7, 13,418, 368, 237,8, 14, 4124, 22) -- все вместе, см. условия
             group by pa.product_id 
            having count(distinct pa.attribute_id) = 8) aa -- поиск по восьми категориям атрибутов
      on p.product_id = aa.product_id
WHERE p.quantity > 0
        AND p.manufacturer_id IN (197, 62, 4)
        AND p.price BETWEEN 32317 
        AND 1000000 GROUP BY p.model LIMIT 0,60



 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
with prod (product_id, name) as 
  (select 1, 'Ferrari' from dual union all
   select 2, 'Mazda'   from dual union all
   select 3, 'Lada'    from dual),
     attr (attribute_id, prop) as
  (select 11, 'Red'   from dual union all
   select 12, 'White' from dual union all
   select 13, 'Blue'  from dual union all
   select 21, 'Fast'  from dual union all
   select 22, 'Slow'  from dual),
     ptoa (product_id,attribute_id) as
  (select 1,11 from dual union all
   select 1,21 from dual union all
   select 2,12 from dual union all
   select 2,22 from dual union all
   select 3,13 from dual union all
   select 3,21 from dual)
select p.* from prod p
inner join (select pa.product_id, 
                   count(distinct pa.attribute_id) 
              from ptoa pa
             where pa.attribute_id in (11,12,13,21) -- 11-13 - все цвета, 21 - только быстрые
             group by pa.product_id 
            having count(distinct pa.attribute_id) = 2) aa
      on p.product_id = aa.product_id

...
Рейтинг: 0 / 0
02.10.2020, 01:47
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004725
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
paver
Гость
para_bit

Код: sql
1.
2.
3.
4.
SELECT p.model,
         p.product_id            
...
GROUP BY p.model


Про какие вы поля, какие не участвуют в группировке

Группируете по model, а в списке селекта используете product_id. Если между product_id и model имеется взаимно однозначное соответствие, то группировать явно лучше по полю int, а не varchar. Тогда и индекс по model не потребуется
...
Рейтинг: 0 / 0
02.10.2020, 05:38
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004726
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
miksoft,

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
SELECT p.model,
         p.product_id            
FROM product p
INNER JOIN product_attribute AS pa1
    ON (p.product_id=pa1.product_id)
INNER JOIN product_attribute AS pa2
    ON (p.product_id=pa2.product_id)
INNER JOIN product_attribute AS pa3
    ON (p.product_id=pa3.product_id)
INNER JOIN product_attribute AS pa4
    ON (p.product_id=pa4.product_id)
INNER JOIN product_attribute AS pa5
    ON (p.product_id=pa5.product_id)
INNER JOIN product_attribute AS pa6
    ON (p.product_id=pa6.product_id)
INNER JOIN product_attribute AS pa7
    ON (p.product_id=pa7.product_id)
INNER JOIN product_attribute AS pa8
    ON (p.product_id=pa8.product_id)
WHERE p.quantity > 0
        AND pa1.attribute_id IN (12, 23)
        AND pa2.attribute_id IN (2, 37, 42, 54, 1)
        AND pa3.attribute_id IN (4, 38)
        AND pa4.attribute_id IN (535)
        AND pa5.attribute_id IN (7, 13)
        AND pa6.attribute_id IN (418, 368, 237)
        AND pa7.attribute_id IN (8, 14, 41)
        AND pa8.attribute_id IN (24, 22) AND p.manufacturer_id IN (197, 62, 4) AND p.price BETWEEN 32317 AND 1000000 GROUP BY p.model LIMIT 0,60
...
Рейтинг: 0 / 0
02.10.2020, 05:53
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004727
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
НеофитSQL,

Число категорий атрибутов действительно известно, но не все атрибуты взаимоисключающие, так, например изделие может быть: Золотое, 585 пробы, иметь 2 камня Сапфир и Бриллиант, и соответственно несколько цветов камней Фиолетовый и Белый.
...
Рейтинг: 0 / 0
02.10.2020, 05:58
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004729
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
para_bit
НеофитSQL,

Число категорий атрибутов действительно известно, но не все атрибуты взаимоисключающие, так, например изделие может быть: Золотое, 585 пробы, иметь 2 камня Сапфир и Бриллиант, и соответственно несколько цветов камней Фиолетовый и Белый.


Это можно привести к взаимоискчающим атрибутам через увеличение числа категорий (материал, проба, тип камня1, тип камня2,..), но выглядит трудоёмко.

Вот ещё одна идея, которую можно попробовать за пару минут: замените все джойны на RIGHT JOIN, оставьте все остальное как было.
Я сейчас не у компа, но учитывая просеивающую форму запроса, это по-моему будет то что надо..
...
Рейтинг: 0 / 0
02.10.2020, 07:24
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004734
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
НеофитSQL
замените все джойны на RIGHT JOIN
Не поясните логику этого (признаться, странного) совета?
...
Рейтинг: 0 / 0
02.10.2020, 08:12
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004735
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
НеофитSQL,

Спасибо, попробовал, но не помогло, время выполнения прежнее
...
Рейтинг: 0 / 0
02.10.2020, 08:20
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004738
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
Посоветовали пару решений на другом форуме, изложу ниже результаты по ним По итогам получилось 3 вида запросов: Запрос с множеством JOIN (1)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

SELECT p.model, p.product_id
FROM oc_product p
LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id)
INNER JOIN oc_product_attribute as pa1 ON (p.product_id=pa1.product_id)
......
INNER JOIN oc_product_attribute as pa8 ON (p.product_id=pa8.product_id)
WHERE p.quantity > 0 AND 
pa1.attribute_id IN (10) 
.....
AND pa8.attribute_id IN (632,103) AND p2c.category_id IN (46, 81, 1)
AND p.manufacturer_id IN (4)
GROUP BY p.model, p.product_id
Запрос (2)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

SELECT model, product_id
FROM oc_product p 
INNER JOIN oc_product_to_category p2c USING (product_id)
INNER JOIN oc_product_attribute pa USING (product_id)
   Where p.quantity > 0 AND p.manufacturer_id IN (4) AND p2c.category_id IN (1, 46, 81)
   GROUP BY model, product_id
HAVING SUM(pa.attribute_id IN (2, 33))
....
AND SUM(pa.attribute_id IN (632, 103))
С подзапросами (3)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SELECT p.model,
         p.product_id
FROM oc_product p
INNER JOIN oc_product_to_category p2c USING (product_id)
INNER JOIN oc_product_attribute pa USING (product_id)
   Where p.quantity > 0 AND p.manufacturer_id IN (4) AND p2c.category_id IN (1, 46, 81)
AND EXISTS(SELECT 1 FROM oc_product_attribute WHERE 
    product_id=p.product_id and attribute_id IN (2, 33))
.....
AND EXISTS(SELECT 1 FROM oc_product_attribute WHERE 
    product_id=p.product_id and attribute_id IN (4, 38))
GROUP BY p.model,
         p.product_id
По итогам тестов пришел к следующим выводам: При работе с количеством атрибутов от 1 до 7
  • Запрос (1) 0.53 сек. - Время выполнения варьируется от от 0.1 до 0.5 сек в зависимости от кол-ва типов атрибутов.
  • Запрос (2) 0.9647 сек. Время выполнения практически одинаковое
  • Запрос (3) 0.6429 сек - Время выполнения практически одинаковое
При работе с количеством атрибутов от 7 до 12
    Запрос (1) > 2 сек Запрос (2) 0.9815 сек - Практически не изменяется Запрос (3) 0.6729 сек - Время выполнения практически одинаковое
Но вопрос остается - неужели нет какого-то другого средства для оптимизации данного вида запроса?
...
Рейтинг: 0 / 0
02.10.2020, 08:23
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004745
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
НеофитSQL,

Все верно, примерно так как Вы и дали запрос: там на самом деле пофиг сколько типов атрибутов у товара 8 или иное число. Джойн ровно один, а вот все остальное регулируется в WHERE и HAVING

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT p.product_id, p.model
  , count(pa.attribute_id) cntAtr -- всего найдено атрибутов
  , SUM( pa.attribute IN(1,2,4,7) ) cntGrpA -- всего найдено атрибутов из группы А
  -- , и т.д. тут набор группировочных условий, в т.ч "свойство ХХХ - обязательно"
FROM product p
INNER JOIN product_attribute pa ON pa.product_id = p.product_id -- джойним с атрибутами

WHERE pa.attribute_id in (1, 2, 4, 7, 8, 12, 13, 14, 22, 23, 37, 38, 42, 54, 237, 368, 418, 535, 4124) -- все вместе, см. условия (distinct sort)

GROUP BY pa.product_id 

HAVING cntAtr=8                                -- только те, где есть ровно 8 атрибутов И все остальные проверки типа cntgrpA = 3

  -- набор общих условий к таблице товаров, кмк, лучше переносить в блок WHERE.
    AND pquantity>0
    AND p.manufacturer_id IN (197, 62, 4)
    AND p.price BETWEEN 32317 AND 1000000
;



При правильно настроенных индексах запросы такого формата работают наиболее оптимальным способом. Вот, по ним и их переработке на оконные функции и предлагаю сделать очередной "фак".. ;)
...
Рейтинг: 0 / 0
02.10.2020, 08:34
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004750
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
В третьем варианте откуда взялась таблица категорий товаров? Если она есть, то да, по ней тоже можно отсеять не нужное.
Особенно, если атрибуты - категорированы и список IN() можно делить по ним.
...
Рейтинг: 0 / 0
02.10.2020, 08:49
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004783
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
Arhat109,

получился вот такой запрос для 8 параметров

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SELECT SQL_NO_CACHE p.product_id, p.model
  , count(pa.attribute_id) cntAtr 
  , SUM( pa.attribute_id IN(2, 33) ) cntGrpA
  , SUM( pa.attribute_id IN(4, 38) ) cntGrpB
  , SUM( pa.attribute_id IN(7, 13) ) cntGrpC
  , SUM( pa.attribute_id IN(8) ) cntGrpD
  , SUM( pa.attribute_id IN(9, 428, 386, 28, 39) ) cntGrpE
  , SUM( pa.attribute_id IN(6,27) ) cntGrpF
  , SUM( pa.attribute_id IN(632,103) ) cntGrpG
  , SUM( pa.attribute_id IN(10) ) cntGrpK
FROM oc_product p
INNER JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
INNER JOIN oc_product_attribute pa ON pa.product_id = p.product_id
WHERE pa.attribute_id in (2, 4, 6, 7, 8, 9, 10, 13, 27, 28, 33, 38, 39, 103, 428, 386, 632)
           AND p.quantity>0
           AND p.manufacturer_id IN (4) 
           AND p2c.category_id IN (1, 46, 81)
GROUP BY p.model, pa.product_id 
HAVING cntAtr > 0 AND cntAtr <= 17
            AND cntGrpA > 0 AND cntGrpA <= 2
            AND cntGrpB> 0 AND cntGrpB <= 2
            AND cntGrpC > 0 AND  cntGrpC <=2
            AND cntGrpD > 0 AND cntGrpD = 1
            AND cntGrpE > 0 AND cntGrpE <= 5
            AND cntGrpF > 0 AND cntGrpF <= 2
            AND cntGrpG > 0 AND cntGrpG <= 2
            AND cntGrpK > 0 AND cntGrpK <= 2



Время выполнения 0.9107 сек. Таблицу категории да, тоже включил
...
Рейтинг: 0 / 0
02.10.2020, 10:02
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004800
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
para_bit,

опишите словами условия в блоке HAVING там у Вас не строгое равенство почему-то. Вы ищете по ИЛИ в такой набор атрибутов ИЛИ из этой группы?
...
Рейтинг: 0 / 0
02.10.2020, 10:59
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004818
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
Arhat109,

отбираем товары, у которых:
Проба 585 или 750
И Метали Золото или Серебро
И размер 17 или 22 или 40 или 50
И Камень Сапфир или Бриллиант
И Цвет Фиолетовый или Белый
....
В рамках одной группы атрибутов может быть, как одно, так и несколько условий, искать можем, как по 585 и 750, так и просто по 585

Т.е. задаем условие поиска в каталоге товаров
...
Рейтинг: 0 / 0
02.10.2020, 11:35
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004823
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
помоему вы фасеты хотите.
...
Рейтинг: 0 / 0
02.10.2020, 11:50
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004826
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
para_bit,

Вот. Значит Вам надо считать не кол-во атрибутов золото оно ИЛИ серебро, а кол-во их категорий
есть И Проба И Метали И размер И Камень И Цвет
в части HAVING, а по или отбор произойдет в WHERE.
Как-то так наверное:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT SQL_NO_CACHE p.product_id, p.model
  , count(pa.attribute_id) cntAtr 
  , SUM( pa.attribute_id IN(2, 33) ) cntGrpA -- пусть будет "проба" (или золото или серебро)
  , SUM( pa.attribute_id IN(4, 38) ) cntGrpB -- а это "метали"
  , SUM( pa.attribute_id IN(7, 13) ) cntGrpC -- а тут "размер"
  , SUM( pa.attribute_id IN(8) ) cntGrpD       -- "камень"
  , SUM( pa.attribute_id IN(9, 428, 386, 28, 39) ) cntGrpE -- "цвет"
  , SUM( pa.attribute_id IN(6,27) ) cntGrpF
  , SUM( pa.attribute_id IN(632,103) ) cntGrpG
  , SUM( pa.attribute_id IN(10) ) cntGrpK
FROM oc_product p
INNER JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
INNER JOIN oc_product_attribute pa ON pa.product_id = p.product_id
WHERE pa.attribute_id in (2, 4, 6, 7, 8, 9, 10, 13, 27, 28, 33, 38, 39, 103, 428, 386, 632)
           AND p.quantity>0
           AND p.manufacturer_id IN (4) 
           AND p2c.category_id IN (1, 46, 81)
GROUP BY p.model, pa.product_id 
HAVING (cntGrpA * cntGrpB * cntGrpC * cntGrpD * cntGrpE * cntGrpF * cntGrpG * cntGrpK) > 0



Произведение даст нуль, если хотя бы одна группа отсутствует.
...
Рейтинг: 0 / 0
02.10.2020, 11:55
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004830
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
Кстати, получилось что cntAttr Вам вообще тут не интересен, сколько их нашлось всего. Можно в SELECT воткнуть логическую функцию И то И это .. и в HAVING проверять только ее истинность.

Сравните с таким же колвом параметров с другими способами .. уже интересно :)
...
Рейтинг: 0 / 0
02.10.2020, 11:58
| Ответить | Цитировать | Написать  
25 сообщений из 44, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько Join таблицы саму на себя
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=40004800&tid=1828360]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 135ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 58ms
get tp. blocked users:
 1ms
others: 14ms
total:  252ms
созд. / загр.: 252ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]