Телеграмм чат группы sqlcom страница 6851

SELECT
    products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as  modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
    brands.name as brand, brands.url as brand_url,
    categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
    pv.price,
    pv.variant_id,
    pv.stock,
    pv.sku,
    pv.old_price,
    pi.price as install_price,
    pi.install_id as install_id,
    pi.city_id as install_city_id,
    pi.ot as install_ot,
    pi.type as install_type,
    media.name as media_image,
    media.id as media_id
FROM products
         LEFT JOIN categories ON categories.category_id = products.category_id
         LEFT JOIN brands ON products.brand_id = brands.brand_id
         LEFT JOIN products_categories ON products.product_id = products_categories.product_id
         LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
         LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
         LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
        categories.enabled = 1
  AND products.enabled = 1
  AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
    (pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
  AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
    CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
    CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

источник

13:29пожаловаться #10

Konstantin Taranov in SqlCom.ru - Стиль жизни SQL

Anton Kartsev AlarmCRM.ru

Коллеги добрый день.
Достался запрос. Запрос выполняется 5 секунд mysql 5.4.
Основная проблема в:

GROUP BY и ORDER BY

Какие индексы нужно создать и помогут ли они?

Сам запрос

SELECT
    products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as  modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
    brands.name as brand, brands.url as brand_url,
    categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
    pv.price,
    pv.variant_id,
    pv.stock,
    pv.sku,
    pv.old_price,
    pi.price as install_price,
    pi.install_id as install_id,
    pi.city_id as install_city_id,
    pi.ot as install_ot,
    pi.type as install_type,
    media.name as media_image,
    media.id as media_id
FROM products
         LEFT JOIN categories ON categories.category_id = products.category_id
         LEFT JOIN brands ON products.brand_id = brands.brand_id
         LEFT JOIN products_categories ON products.product_id = products_categories.product_id
         LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
         LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
         LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
        categories.enabled = 1
  AND products.enabled = 1
  AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
    (pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
  AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
    CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
    CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

ну во-первых, вам сюда https://t.me/ru_mysql
а во-вторых, рекомендовать индексы по одному group by это лучше к гадалкам

ru_mysql

По-русски о MySQL/Percona/MariaDB. Новостной канал: https://t.me/ru_mysql_ch
/report в ответ на спам сообщение

источник

13:31пожаловаться #11

Anton Kartsev AlarmC... in SqlCom.ru - Стиль жизни SQL

Спасибо

источник

13:31пожаловаться #12

Ilia Zviagin in SqlCom.ru - Стиль жизни SQL

Anton Kartsev AlarmCRM.ru

Коллеги добрый день.
Достался запрос. Запрос выполняется 5 секунд mysql 5.4.
Основная проблема в:

GROUP BY и ORDER BY

Какие индексы нужно создать и помогут ли они?

Сам запрос

SELECT
    products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as  modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
    brands.name as brand, brands.url as brand_url,
    categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
    pv.price,
    pv.variant_id,
    pv.stock,
    pv.sku,
    pv.old_price,
    pi.price as install_price,
    pi.install_id as install_id,
    pi.city_id as install_city_id,
    pi.ot as install_ot,
    pi.type as install_type,
    media.name as media_image,
    media.id as media_id
FROM products
         LEFT JOIN categories ON categories.category_id = products.category_id
         LEFT JOIN brands ON products.brand_id = brands.brand_id
         LEFT JOIN products_categories ON products.product_id = products_categories.product_id
         LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
         LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
         LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
        categories.enabled = 1
  AND products.enabled = 1
  AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
    (pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
  AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
    CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
    CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

Надо полный запрос...

источник

15:11пожаловаться #13

Anton Kartsev AlarmC... in SqlCom.ru - Стиль жизни SQL

Ilia Zviagin

Надо полный запрос...

Дополнил

источник

15:19пожаловаться #14

Ilia Zviagin in SqlCom.ru - Стиль жизни SQL

Anton Kartsev AlarmCRM.ru

Коллеги добрый день.
Достался запрос. Запрос выполняется 5 секунд mysql 5.4.
Основная проблема в:

GROUP BY и ORDER BY

Какие индексы нужно создать и помогут ли они?

Сам запрос

SELECT
    products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as  modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
    brands.name as brand, brands.url as brand_url,
    categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
    pv.price,
    pv.variant_id,
    pv.stock,
    pv.sku,
    pv.old_price,
    pi.price as install_price,
    pi.install_id as install_id,
    pi.city_id as install_city_id,
    pi.ot as install_ot,
    pi.type as install_type,
    media.name as media_image,
    media.id as media_id
FROM products
         LEFT JOIN categories ON categories.category_id = products.category_id
         LEFT JOIN brands ON products.brand_id = brands.brand_id
         LEFT JOIN products_categories ON products.product_id = products_categories.product_id
         LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
         LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
         LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
        categories.enabled = 1
  AND products.enabled = 1
  AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
    (pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
  AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
    CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
    CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

Предлагаю перенести в тематический чат https://t.me/mysql_db

MySQL

English group: @mysql_en

Группа о СУБД MySQL.

Правила: https://t.me/mysql_db/68226

Часто задаваемые вопросы: https://git.io/fjLbO

Админы: @smlkw @Piterden @MasterZiv

источник

15:23пожаловаться #15

Ilia Zviagin in SqlCom.ru - Стиль жизни SQL

Anton Kartsev AlarmCRM.ru

Коллеги добрый день.
Достался запрос. Запрос выполняется 5 секунд mysql 5.4.
Основная проблема в:

GROUP BY и ORDER BY

Какие индексы нужно создать и помогут ли они?

Сам запрос

SELECT
    products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as  modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
    brands.name as brand, brands.url as brand_url,
    categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
    pv.price,
    pv.variant_id,
    pv.stock,
    pv.sku,
    pv.old_price,
    pi.price as install_price,
    pi.install_id as install_id,
    pi.city_id as install_city_id,
    pi.ot as install_ot,
    pi.type as install_type,
    media.name as media_image,
    media.id as media_id
FROM products
         LEFT JOIN categories ON categories.category_id = products.category_id
         LEFT JOIN brands ON products.brand_id = brands.brand_id
         LEFT JOIN products_categories ON products.product_id = products_categories.product_id
         LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
         LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
         LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
        categories.enabled = 1
  AND products.enabled = 1
  AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
    (pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
  AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
    CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
    CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

Ну и в первую очередь у тебя запрос неверный.

Неправильно условия в WHERE и LEFT JOIN -ы написаны

источник

15:24пожаловаться #16

Ilia Zviagin in SqlCom.ru - Стиль жизни SQL

Anton Kartsev AlarmCRM.ru

Коллеги добрый день.
Достался запрос. Запрос выполняется 5 секунд mysql 5.4.
Основная проблема в:

GROUP BY и ORDER BY

Какие индексы нужно создать и помогут ли они?

Сам запрос

SELECT
    products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as  modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
    brands.name as brand, brands.url as brand_url,
    categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
    pv.price,
    pv.variant_id,
    pv.stock,
    pv.sku,
    pv.old_price,
    pi.price as install_price,
    pi.install_id as install_id,
    pi.city_id as install_city_id,
    pi.ot as install_ot,
    pi.type as install_type,
    media.name as media_image,
    media.id as media_id
FROM products
         LEFT JOIN categories ON categories.category_id = products.category_id
         LEFT JOIN brands ON products.brand_id = brands.brand_id
         LEFT JOIN products_categories ON products.product_id = products_categories.product_id
         LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
         LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
         LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
        categories.enabled = 1
  AND products.enabled = 1
  AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
    (pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
  AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
    CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
    CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

FROM products
LEFT JOIN categories ON categories.category_id = products.category_id

и тут же

WHERE
categories.enabled = 1

Либо условие должно быть в ON join-а, либо JOIN не должен быть OUTER

источник

15:25пожаловаться #17

Timus in SqlCom.ru - Стиль жизни SQL

Ilia Zviagin

Ну и в первую очередь у тебя запрос неверный.

Неправильно условия в WHERE и LEFT JOIN -ы написаны

а вот это тебя не сущает? pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL
ну да ладно. оффтопим)

источник

15:28пожаловаться #18

Ilia Zviagin in SqlCom.ru - Стиль жизни SQL

Timus

а вот это тебя не сущает? pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL
ну да ладно. оффтопим)

Да меня всё смущает, но сказал пока только про первое

источник

15:28пожаловаться #19

Elvira in SqlCom.ru - Стиль жизни SQL

Святая пюрешечка, что это

источник

15:28пожаловаться #20