ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько Join таблицы саму на себя
44 сообщений из 44, показаны все 2 страниц
  все  
Несколько 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
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004850
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
ScareCrow,

Как понял "не совсем". Фасеты (мож ошибаюсь) это последовательный параметризованный поиск. Выбрали срез - показали (заодно сохранили, урезав выборку) и так постепенно добавляем параметры отбора.

Это полноценный параметризованный поиск по параметрам - выбрали набор параметров и жмакнули "и-щи!" получили результом только то что надо.

Оба подхода имеют место быть и у каждого свои плюсики, как и минусики, кмк. :)
...
Рейтинг: 0 / 0
02.10.2020, 12:53
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004861
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
даже по вашему описанию разницу не видно.
...
Рейтинг: 0 / 0
02.10.2020, 13:17
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004865
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
ScareCrow,
Разница в реализации. В фасете делаем несколько стартовых view с начальным параметром и прячем в кеш (типо для ускорения первичной выборки), а во втором случае сразу прописываем сложный запрос с кучей параметров и получаем весь результ на руки единовременно.

Фасетный поиск, как вариант, ещё вместо выборок в кеш сохраняют отдельные профили индексов "типа предвыборка".. в любом разе, это несколько последовательных этапов (запросов) по 1шт на новый параметр на базе предыдущей выборки.

.. ну это как делал, как понимаю .. оф. терминология (Вики):

".. Фасетный поиск позволяет перемещаться по многомерному информационному пространству через объединение текстового поиска с постепенным сужением выбора в каждом измерении .."

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

То что тут - это одновременная (разовая) выборка по всему комплекту сразу... собственно "вся разница". :)
...
Рейтинг: 0 / 0
02.10.2020, 13:28
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004871
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
ScareCrow,

добрый день, я такое из коробки только про Битрикс слышал, я так понимаю, что реализовать это не так просто, подскажите, что почитать?
...
Рейтинг: 0 / 0
02.10.2020, 13:44
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004909
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
Arhat109,

не совсем понял о какой логической функции в SELECT вы говорите?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT SQL_NO_CACHE p.product_id, p.model 
  , 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_attribute pa ON pa.product_id = p.product_id
INNER JOIN oc_product_to_category p2c ON  p2c.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.9268 сек. т.е. похоже, что доп. условия не влияли на скорость фильтрации
...
Рейтинг: 0 / 0
02.10.2020, 15:02
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004926
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
para_bit,
Эта модификация и не должна быть быстрее, разве что очень незначительно, т.к. объем выборки тут не изменен по сравнению в пред. вариантом - группируются теже строки. Тут просто наглядней что суммирование - это вычисление попадания в группы, наличие которых Вы потом требуете в HAVING.
Так кмк наглядней что ищется.

Меня интересовало сравнение с другими вариантами запросов, что Вы привели раньше, для одних и тех же данных и условий поиска.
Особенно с вариантом 3.
...
Рейтинг: 0 / 0
02.10.2020, 15:48
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004940
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
para_bit
ScareCrow,

добрый день, я такое из коробки только про Битрикс слышал, я так понимаю, что реализовать это не так просто, подскажите, что почитать?


штатно это есть в Эластике и Сфинксе.
...
Рейтинг: 0 / 0
02.10.2020, 16:29
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004970
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
Akina
НеофитSQL
замените все джойны на RIGHT JOIN
Не поясните логику этого (признаться, странного) совета?


Этот совет следует логике фасетной фильтрации (я не знал этого термина), которая обсуждалась позже в этой теме.

Будучи новичком в SQL, я пока некомфортно отношусь к огромному числу комбинаторных вариантов порожденными inner join восьмой степени. Ошибка в условии WHERE может привести в взрывному (миллионы раз) увеличению числа строк.

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


Я понимаю оптимизатор SQL это умная штука, и может исполнять inner join очень эффективно, несмотря на кажущиеся миллиарды комбинаций в десятой степени. ТС сравнил скорость inner/right join, скорость оказалась одинаковой - это говорит о качестве оптимизатора.

Надеюсь, ответил на ваш вопрос.
...
Рейтинг: 0 / 0
02.10.2020, 18:07
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40004997
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
Arhat109,

Код: sql
1.
InnoDb и даже лучше в xtraDb


ну это как бы одно "яйцо")
...
Рейтинг: 0 / 0
02.10.2020, 19:42
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005190
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
НеофитSQL
Надеюсь, ответил на ваш вопрос.
Это да... впрочем, ответ, уж извините, "для галочки" - да, теперь нельзя сказать, что вопрос проигнорирован.

Но я навылет не понимаю, где хоть какая-то точка соприкосновения между различиями в логике получения результатов (вернее, каким условиям отвечают записи, различающие наборы для внутреннего и правого связываний) и этим обоснованием. "Скажите, а вы не пробовали мочу молодого поросёнка?" (с)

Что же до тутошнего, частного, случая:
Код: sql
1.
2.
3.
4.
SELECT ...
FROM product p
INNER JOIN ...
WHERE p.quantity > 0 ...


Продолжать?
...
Рейтинг: 0 / 0
03.10.2020, 21:14
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005205
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
Akina,

лол, я как раз вернулся дополнить мое сообщение. меа кулпа.

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

Вот что я надеялся сделать. Привожу несколько строчек от ТС для удобства обсуждения:
Код: sql
1.
2.
3.
        AND pa1.attribute_id IN (12, 23)
        AND pa2.attribute_id IN (2, 37, 42, 54, 1)
        AND pa3.attribute_id IN (4, 38)



Начать с таблицы product_to_attribute. Там более полумиллиона строчек.

1) отсеять из связочной таблицы product_to_attribute все товары, у которых нет атрибута 12 или 23. Строчек стало меньше
2) отсеять все товары, у которых нет атрибутов 2, или 37, или 42, или 54, или 1
3) отсеять все товары, у которых нет атрибутов 4 или 38.
.. и т.д.

Не заменяйте нна лефт джойн, это было неверно.
...
Рейтинг: 0 / 0
03.10.2020, 22:49
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005229
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
НеофитSQL,

Разве такое не полностью эквивалентно одному in() с перечислением ВСЕХ идентов атрибутов? И оптимизатор не преобразует такой набор AND в один просмотр?

Подозреваю, что преобразует, т.к. на Ix86 наборе команд есть префикс повторения, с помощью которого поиск значения в списке (наборе) делается вовсе "одной командой" .. ну, по крайней мере, соблазн велик.
...
Рейтинг: 0 / 0
04.10.2020, 05:56
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005232
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
Alex_Ustinov,
почти. Тут непонятно какая версия мускуля, перконы или марии в пользовании.
Ну и ещё вспомнилось (не знаю как сейчас), MyISAM имеет плохое масштабирование к количеству приджойненных табличек и особенно в режимах GROUP BY/HAVING .. я бы все же перешел на другой движок.
...
Рейтинг: 0 / 0
04.10.2020, 08:19
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005236
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
НеофитSQL
Начать с таблицы product_to_attribute. Там более полумиллиона строчек.

1) отсеять из связочной таблицы product_to_attribute все товары, у которых нет атрибута 12 или 23. Строчек стало меньше
2) отсеять все товары, у которых нет атрибутов 2, или 37, или 42, или 54, или 1
3) отсеять все товары, у которых нет атрибутов 4 или 38.
.. и т.д.
Подобные рассуждения сразу говорят о том, что есть опыт процедурного программирования, но нет понимания, как работает SQL-сервер "унутре". Привычка мыслить построчно обрабатываемым массивом, а не набором записей, обрабатываемым как единое целое.

"Отсекаем всё лишнее" - это и про SQL.

Если же попытаться серверу "помочь", заставив его хитровывернутыми конструкциями делать вот именно так - практически наверняка станет только хуже.
...
Рейтинг: 0 / 0
04.10.2020, 09:51
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005237
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
<dup - del>
...
Рейтинг: 0 / 0
04.10.2020, 09:51
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005257
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
ScareCrow,

спасибо, буду разбираться.
...
Рейтинг: 0 / 0
04.10.2020, 12:43
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005258
para_bit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
para_bit
Гость
Arhat109
Alex_Ustinov,
почти. Тут непонятно какая версия мускуля, перконы или марии в пользовании.
Ну и ещё вспомнилось (не знаю как сейчас), MyISAM имеет плохое масштабирование к количеству приджойненных табличек и особенно в режимах GROUP BY/HAVING .. я бы все же перешел на другой движок.


версия MySQL указана в первом сообщении 5.7.31.
...
Рейтинг: 0 / 0
04.10.2020, 12:44
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005328
Arhat109
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Arhat109
Участник
para_bit,

В таком разе, как понимаю innoDb и xtraDb несколько не одно и тоже.. но пусть меня поправят, если ошибся.
...
Рейтинг: 0 / 0
04.10.2020, 22:26
| Ответить | Цитировать | Написать  
Несколько Join таблицы саму на себя
    #40005332
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
там нет xtraDB
это конкурирующий движок
...
Рейтинг: 0 / 0
04.10.2020, 23:16
| Ответить | Цитировать | Написать  
44 сообщений из 44, показаны все 2 страниц
  все  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько Join таблицы саму на себя
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=47&tid=1828360]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 56ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 78ms
get tp. blocked users:
 2ms
others: 260ms
total:  439ms
созд. / загр.: 439ms
x
x
Закрыть


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