Прожектор Бритни Спирс

Цитата

Как сохранить полностью логику этого запроса без изменений базовых таблицы движка и отдать быстро эти данные холодными без всяких кешей ?

"Загадка от Жака Фреско. На размышление 30 секунд".

 

Решение в лоб - обновление кеша по cron раз в сутки, 10 сек отработает глубокой ночью - не страшно.

 

Или отказаться от JOINов и попробовать простыми запросами, сравнить время.

 

Я не гуру, но может вместо "LIMIT 0, 3" написать "LIMIT 3"? Это даст какое то различие? Интересно было бы узнать.

 

Цитата

А теперь вопрос знатокам...

Точно не ко мне

Ну как минимум первое выпилить NOW() из-запроса. Какая нам разница это было прям сейчас или сегодня или вчера или неделю, если выборка за целый месяц. И записать this->now = 'd-m-y 00:00:00' или как там, навскидку не вспомню.

Если у нас не мультимагазин выпилить p2s вместе с его LEFT JOIN

Quote

Если что, мы с 6 сек на этом реализации, получили 0.18 мс. 

подобное время возможно при использовании предварительно вычисленных агрегатов, например. Как заметили выше - это самое очевидное и простое решение. При работе с ними, весь этот запрос превращается во что-то вроде select product_id as popular from agregate_table where category_id\path_id = ?.

Как альтернативный вариант - использование иных хранилищ, взамен mysql (на мой взгляд сложнее + так же требует синхронизации данных, как и агрегаты - пересоздания)

 

ради спортивного интереса чуть переписал этот кривенький запрос:

а) вычисление популярных товаров из секции select можно вынести в отдельный джоин

б) подсчет количества заказов для каждого товара категории - это не совсем корректный признак его популярности, в то время как сумма купленного количества товара должна быть более точной характеристикой. Потому Count(op.order_id) AS popular заменил на SUM(op.quantity) AS popular. На скорость это не влияет.

Spoiler

SELECT SQL_NO_CACHE
  p.product_id
FROM oc_category_path cp
  JOIN oc_product_to_category p2c
    ON (cp.category_id = p2c.category_id)
  JOIN oc_product p
    ON (p2c.product_id = p.product_id)
  JOIN oc_product_description pd
    ON (p.product_id = pd.product_id)
  JOIN oc_product_to_store p2s
    ON (p.product_id = p2s.product_id)
  JOIN (SELECT
      op.product_id,
      SUM(op.quantity) AS popular
    FROM oc_order_product op
      JOIN `oc_order` o
        ON (op.order_id = o.order_id)
    WHERE o.order_status_id > '0' AND Adddate(o.date_added, INTERVAL 30 day) < Now()
    GROUP BY op.product_id) popular
    ON p.product_id = popular.product_id
WHERE pd.language_id = '1'
AND p.status = '1'
AND p2s.store_id = '0'
-- подставить свои значения или выполнить так, типа, для вообще всех товаров, что бы посложнее 
-- AND p2c.category_id = ?
-- AND cp.path_id = ?
GROUP BY p.product_id
ORDER BY (p.quantity > 0) DESC,
popular.popular DESC,
LCASE(pd.name) DESC,
p.date_added DESC
LIMIT 0, 3;

 

Результаты на живом примере:

при 380к активных и включенных товаров в oc_products и 40к+ заказов в oc_order время выполнения этого запроса без привязки к какой-либо категории чуть менее 0.4сек.

 

С уточнением категории, разумеется, будет быстрее: с потенциальным минимумом, равным времени выполнения запроса из джоина с вычислением популярных товаров (на моих данных около 0.2сек). Если же этот джоин с вычислением popular заменить предварительно посчитанной таблицей, то итоговое время выполнения запроса будет еще меньше: ~0.01сек для категории с 1к товаров, например или около 0.2сек для определения топ3 среди всех 380к товаров.

12 часов назад, niger сказал:

Ну как минимум первое выпилить NOW() из-запроса

Вы адепт секты "кеш mysql"?

Так я вам открою военную тайну. Использование кеша мускуля, это забивание гвоздей микроскопом.

5 минут назад, Vladzimir сказал:

Вы адепт секты "кеш mysql"?

Так я вам открою военную тайну. Использование кеша мускуля, это забивание гвоздей микроскопом.

Да-да.
Мы в курсе мнения вашей секты, что кэш вообще очень вредная вещь, и придумали его вредители, исключительно для того, чтобы не давать грести бабло оптимизаторам вроде вас и Ёды.

3 минуты назад, Shureg сказал:

Да-да.
Мы в курсе мнения вашей секты, что кэш вообще очень вредная вещь, и придумали его вредители, исключительно для того, чтобы не давать грести бабло оптимизаторам вроде вас и Ёды.

А по существу? Я делал десяток тестов, которые всегда показывали, что включение кеша мускуля увеличивает время генерации страницы на 15-20%.

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

1 минуту назад, Vladzimir сказал:

А по существу? Я делал десяток тестов, которые всегда показывали, что включение кеша мускуля увеличивает время генерации страницы на 15-20%.

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

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

1 минуту назад, Shureg сказал:

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

Т.е. получается именно экономия на спичках. И кеш кешу рознь.Но мой коммент был именно к

Цитата

Ну как минимум первое выпилить NOW() из-запроса

Прироста это не даст, от слова совсем.

4 минуты назад, Shureg сказал:

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

Да и глобальная проблема кеша мускуля именно в том, что он дропается глобально, для всей таблицы, при внесении любого изменения в нее. Например инкримент просмотра или вычитание товара со склада делает это занятие безсмысленным.

7 минут назад, Vladzimir сказал:

Прироста это не даст, от слова совсем.

Ну если даже дефолтный кэш mysql старательно убить, то да, совсем не даст. А если хотя бы его оставить, то очень даже даст. 
У вас железная логика - кэш бесполезен, потому что без допилов для его использования не срабатывает.
А допилы, делающие кэш полезным, бесполезны, потому что без без кэша ничего не ускоряют.
Логично, но, как вы говорите, "безсмысленно"

Изменено 25 февраля 2021 пользователем Shureg

5 минут назад, Vladzimir сказал:

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

Для "всей таблицы" это не так уж "глобально".  ))

1 минуту назад, Shureg сказал:

Ну если даже дефолтный кэш mysql старательно убить, то да, совсем не даст. А если хотя бы его оставить, то очень даже даст. 
У вас железная логика - кэш бесполезен, потому что без допилов для его использования не срабатывает.
А допилы, делающие кэш полезным, бесполезны, потому что без без кэша ничего не ускоряют.
Логично, но, как вы говорите, "безсмысленно"

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

16 минут назад, Vladzimir сказал:

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

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

8 часов назад, 100napb сказал:

Результаты на живом примере:

А теперь еще замените

AND Adddate(o.date_added, INTERVAL 30 day) < Now()

на

AND o.date_added > '2020-01-25 00:00:00'

 

1 hour ago, SooR said:

А теперь еще замените

решил посмотреть как изменятся конкретные цифры. Спасибо. Спасибо потому, что

Во-1: я немного ошибся в результатах на живом примере, которые приводил выше. они были приведены для более тяжелого варианта запроса, в котором было отключено уточнение по дате и запрос, по сути, считал вообще все заказы *смущенный смайл*. Ну на коленке же тестируем, ради интереса... Зато! С уточнением по дате, как и было в изначальном условии задачи, запрос работает даже без промежуточных таблиц быстрее: менее 0.1сек. Не так уж и плохо, хотя с агрегатом можно еще быстрее.

Во-2: от варианта записи даты через adddate-interval или явно, результат 0.1сек прям заметно не меняется. хотя я согласен - должно быть быстрее, если указывать дату явно, как Вы и уточнили.

 

 

Изменено 25 февраля 2021 пользователем 100napb

@100napb , у меня ваш запрос выполняется за 0.890, тогда как оригинальный 0.609 (с указанием path_id)

@SooR

¯\_(ツ)_/¯

могу лишь такое сравнение привести со своей стороны

Spoiler

с указанием path_id. результаты выполнения одинаковые

 

 

 

 а с промежуточной таблицей? хотя это уже почти агрегат... с ней не интересно

Spoiler

на случай если лень писать

 

CREATE TABLE IF NOT EXISTS aggr_table (
  PRIMARY KEY (product_id)
) AS (SELECT
   op.product_id, COUNT(op.order_id) AS popular
  FROM oc_order_product op
    LEFT JOIN `oc_order` o
      ON (op.order_id = o.order_id)
  WHERE Adddate(o.date_added, INTERVAL 30 day) < Now()
  AND o.order_status_id > '0'
  GROUP BY op.product_id);

 

 

+

 

SELECT SQL_NO_CACHE
  p.product_id, popular.popular
FROM oc_category_path cp
  JOIN oc_product_to_category p2c
    ON (cp.category_id = p2c.category_id)
  JOIN oc_product p
    ON (p2c.product_id = p.product_id)
  JOIN oc_product_description pd
    ON (p.product_id = pd.product_id)
  JOIN oc_product_to_store p2s
    ON (p.product_id = p2s.product_id)
  JOIN aggr_table as popular
    ON p.product_id = popular.product_id
WHERE pd.language_id = '1'
AND p.status = '1'
AND p2s.store_id = '0'
AND cp.path_id = 18
GROUP BY p.product_id
ORDER BY (p.quantity > 0) DESC,
popular.popular DESC,
LCASE(pd.name) DESC,
p.date_added DESC
LIMIT 0, 3; 

Изменено 25 февраля 2021 пользователем 100napb

Мда уж, почитал ваш холивар.
Смешно.

 

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

 

Второй тупочет ножкой зачем-то про нативный кеш mysql. Не особо понимая, что кеш ничем не помогает, когда у тебя 3000 запросов по 0.001сек, это 3 сек, и кешируй их не кешируй, тут только работать в сторону уменьшения количества запросов. 

Еще один хочет что-то выпилить, и предлагает кэпские решения, которые итак понятны, но ни на что не влияют, так как проблема всей этой конструкции, в том, что на 100 000 товаров производится 100 000 подзапросов count, по полю другой таблицы, в которой 140 000 записей, и вот в этом  самая большая проблема.

В том что каждый раз, на каждую категорию производится вот этот вот COUNT(order_id). 

 

Ну и здесь есть несколько вариантов решений:

1 - просто забить и сделать именно для этих результатов длинный кеш, скажем на сутки. Но у нас 300-400 пользователей получат долгую загрузку страницы, больше 5-6 сек.

2 - Запустить плановую агрегацию, крон, который будет перебирать значения и считать их раз в сутки под каждую категорию. Но у нас есть проблема, запрос 6 секунд, на 300  категорий, это 1800 секунд у нас занято ядро сервера, раз в сутки, которое будет просто в холостую гонять эти каунты.

3 - Cамая пожалуй верная реализация, это сделать промежуточную PIVOT таблицу, в которую мы посчитаем общее количество продаж по каждому товару за последний месяц, одним длинным запросов в 5-7 секунд. А потом уже будем выгребать данные из нее, учитывая что у нас в ней сразу есть все поля как для сортировки так и для группировки, mysql сделает это очень быстро с использованием составных индексов, на прототипе у меня вышло, что то около 0.18сек, вместо наших исходных шести. И вот потом уже можно эти данные кешировать в самом движке, либо прогреть их в какую-то агрегатную таблицу, и оттуда забирать дополнительным запросом, но так или иначе, мы сэкономим ресурс сервера (проект нагруженный), и отдадим пользователям быстрые страницы, ровно с теми данными, которые изначально нам были необходимы.

 

Хейтеры, в бой, можете начинать рассказывать какие вы умные, а я тупорылый! 

В 27.02.2021 в 12:31, ****** сказав:

 

Еще один хочет что-то выпилить, и предлагает кэпские решения, которые итак понятны, но ни на что не влияют, так как проблема всей этой конструкции, в том, что на 100 000 товаров производится 100 000 подзапросов count, по полю другой таблицы, в которой 140 000 записей, и вот в этом  самая большая проблема.

 

 

Хейтеры, в бой, можете начинать рассказывать какие вы умные, а я тупорылый! 

"не то что бы я хейтер, но"

 

если товаров не 100 000, а только 1000 - тогда уменьшения кеши помогает ?

 

 

В 07.06.2021 в 21:20, NazarVen сказал:

"не то что бы я хейтер, но"

 

если товаров не 100 000, а только 1000 - тогда уменьшения кеши помогает ?

 

 

 

Непонятный текст.

Парни такая же ерунда. Товаров 700К+ в категориях есть по 50~70 товаров. С кэшем все прекрасно но чтоб туда опасть нужно загрузить страницу. Товары летают, с этим не проблем, но категории, это ппц.