Медленный COUNT(*) на большем количестве записей и GROUP BY

[CyanoFresh]

Всем привет.
Есть такая структура



запросы:



Индексы:



когда товаров и данных мало - все летает, а когда ~200000 - начинаются проблемы, как на скрине.
Получилось немного оптимизировать, заменив DISTINCT на group by `id` (вопрос: правильно ли?).
В SELECT'e убрал ORDER BY `sort_order`, было 900мс, стало ~5мс (вопрос: в чем проблема? индекс на sort_order есть)
но count запрос больше не получается оптимизировать.

В архитектуре БД я недавно, подскажите, пожалуйста, что сделано не так.

[Nex-Otaku]

Зачем вложенный запрос? Делайте сразу select count(product.id).

[CyanoFresh]

Зачем вложенный запрос? Делайте сразу select count(product.id).

но тогда нельзя будет фильтровать по параметрам

[CyanoFresh]

Зачем вложенный запрос? Делайте сразу select count(product.id).

может можно как то проверять, есть ли у товара параметр без JOIN? Сейчас такой запрос:

Код: Выделить всё

SELECT `product`.`id`, `product`.`price` FROM `product` LEFT JOIN `product_parameter_value` ON `product`.`id` = `product_parameter_value`.`product_id` LEFT JOIN `parameter_value` ON `product_parameter_value`.`parameter_value_id` = `parameter_value`.`id` WHERE (`product`.`status`=10) AND (((`parameter_value`.`id` = 132) AND (`parameter_value`.`parameter_id` = 14)) OR ((`parameter_value`.`id` = 129) AND (`parameter_value`.`parameter_id` = 15))) AND (`product`.`category_id`=9) GROUP BY `id` LIMIT 20

[someweb]

Закэшировать нельзя? У меня похожей сложности запрос на таблицу в 50 000 записей 700 мс после сброса кэша (core i5 2.9 обычный HDD)

[CyanoFresh]

Закэшировать нельзя? У меня похожей сложности запрос на таблицу в 50 000 записей 700 мс после сброса кэша (core i5 2.9 обычный HDD)

думаю, можно. Но ведь тогда количество может быть не актуальным (а нужно ли актуальное количество)? Под каждую галочку фильтра он будет создавать свой кеш?

[someweb]

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

[ElisDN]

Подключите ElasticSearch и денормализируйте всё в него.

[CyanoFresh]

Подключите ElasticSearch и денормализируйте всё в него.

спасибо, наверное так и сделаю, если больше ничего не получиться

[Nex-Otaku]

Зачем вложенный запрос? Делайте сразу select count(product.id).

но тогда нельзя будет фильтровать по параметрам

Если вы измените запрос, в котором COUNT, у вас что-то станет "нельзя"? С чего вы это взяли?

[CyanoFresh]

Зачем вложенный запрос? Делайте сразу select count(product.id).

но тогда нельзя будет фильтровать по параметрам

Если вы измените запрос, в котором COUNT, у вас что-то станет "нельзя"? С чего вы это взяли?

хм, наверно неправильно вас понял.

Этот запрос генерирует dataprovider по умолчанию. Если сделать:

Код: Выделить всё

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'totalCount' => $query->count(),
        ]);

запрос получится тот же самый. group by и count как то не совмещаются

[Nex-Otaku]

Как выглядит изначальный запрос ActiveQuery?

Не должен он такое генерировать, вложенный запрос.

[CyanoFresh]

Код: Выделить всё

	$query = $category
            ->getActiveProducts()
            ->select(['product.id', 'product.price'])
            ->with([
                'productDescription' => function (ActiveQuery $query) {
                    $query->select(['name', 'slug', 'product_id']);
                },
            ])
            ->groupBy('product.id');

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

если убрать groupBy - запрос становится не вложенным

[Nex-Otaku]

Это вообще не тот запрос. Какой ещё "name", "slug"? Из того, что вы привели сейчас, в принципе не могу сформироваться тот запрос, который в первом посте.

Ну или тот запрос для вас уже не актуален, тогда скажите какой SQL код генерируется для этого запроса. И заодно, что там в "getActiveProducts" и в связи "productDescription".

Вообще не должен никакой вложенный запрос появляться. Как он у вас появился, я пока не понимаю. Покажите пожалуйста подробности, может это баг Yii очередной ) Или у вас что-то неоптимально написано.

[CyanoFresh]

Это вообще не тот запрос. Какой ещё "name", "slug"? Из того, что вы привели сейчас, в принципе не могу сформироваться тот запрос, который в первом посте.

Ну или тот запрос для вас уже не актуален, тогда скажите какой SQL код генерируется для этого запроса. И заодно, что там в "getActiveProducts" и в связи "productDescription".

Вообще не должен никакой вложенный запрос появляться. Как он у вас появился, я пока не понимаю. Покажите пожалуйста подробности, может это баг Yii очередной ) Или у вас что-то неоптимально написано.

вот нашел похожую проблему: https://stackoverflow.com/questions/243 ... by-in-yii2

мой код:

Код: Выделить всё

    /**
     * @return ProductQuery|\yii\db\ActiveQuery
     */
    public function getProducts()
    {
        return $this->hasMany(Product::className(), ['category_id' => 'id'])->inverseOf('category');
    }

    /**
     * @return ProductQuery|\yii\db\ActiveQuery
     */
    public function getActiveProducts()
    {
        return $this->getProducts()->active();
    }

связь productDescription:

Код: Выделить всё

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getProductDescription()
    {
        return $this->hasOne(ProductDescription::className(), ['product_id' => 'id'])->inverseOf('product');
    }

ProductDescription - обычная отдельная таблица, чтобы не хранить много текста в основной таблице. Выбирается 3 поля только чтобы не тянуть на этой странице ненужные данные.

Какой запрос формируется - есть в скринах в первом посте, не обманываю же я

[Nex-Otaku]

Тут не обман, тут путаница. Не может из такого запроса

Код: Выделить всё

->getActiveProducts()
            ->select(['product.id', 'product.price'])
            ->with([
                'productDescription' => function (ActiveQuery $query) {
                    $query->select(['name', 'slug', 'product_id']);
                },
            ])
            ->groupBy('product.id');

Генерироваться такой SQL


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

[CyanoFresh]

Тут не обман, тут путаница. Не может из такого запроса

Код: Выделить всё

->getActiveProducts()
            ->select(['product.id', 'product.price'])
            ->with([
                'productDescription' => function (ActiveQuery $query) {
                    $query->select(['name', 'slug', 'product_id']);
                },
            ])
            ->groupBy('product.id');

Генерироваться такой SQL


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

да, извините, забыл. Переделал, чтобы если фильтры не выбраны - не джоинить таблицы с параметрами. При том коде, что показывал, такой запрос получается:



Все равно вложенный query