1 Million products!

george

There is the query that executes 2 seconds on opening "product form":

select * from product_flat where url_key = '7637325' and locale = 'en' and channel = 'default'

There are 330 000 products already.

george

just added indexes to fields 'url_key', 'locale', 'channel',
now query lasts only 0.0053 sec!
Note that to my 300mb base added 30mb of indexes.

george

In admin panel, products section page (/admin/catalog/products)

5 seconds! /packages/Webkul/Ui/src/DataGrid/DataGrid.php:262
select count(*) as aggregate from product_flat left join products on product_flat.product_id = products.id left join attribute_families on products.attribute_family_id = attribute_families.id left join product_inventories on product_flat.product_id = product_inventories.product_id where channel = 'default' and locale = 'en' group by product_flat.product_id

plus

7 seconds! /packages/Webkul/Ui/src/DataGrid/DataGrid.php:262
select product_flat.product_id as product_id, product_flat.sku as product_sku, product_flat.name as product_name, products.type as product_type, product_flat.status, product_flat.price, attribute_families.name as attribute_family, SUM(product_inventories.qty) as quantity from product_flat left join products on product_flat.product_id = products.id left join attribute_families on products.attribute_family_id = attribute_families.id left join product_inventories on product_flat.product_id = product_inventories.product_id where channel = 'default' and locale = 'en' group by product_flat.product_id order by product_id desc limit 20 offset 0

please, help me to optimize this for big catalog.

george

In file /packages/Webkul/Ui/src/DataGrid/DataGrid.php:

abstract class DataGrid -> public function getCollection()

this function is slow on big amount of products

$this->collection = $this->queryBuilder->orderBy($this->index, $this->sortOrder)->paginate($this->itemsPerPage)->appends(request()->except('page'))

it's generate aggregation query:

select count(*) as aggregate from product_flat left join products on product_flat.product_id = products.id left join attribute_families on products.attribute_family_id = attribute_families.id left join product_inventories on product_flat.product_id = product_inventories.product_id where channel = 'default' and locale = 'en' group by product_flat.product_id

on my 700 000 products it takes 8sec.
please, help to get faster this query on big catalog!

Second query become faster than comment code in public function prepareQueryBuilder() in class ProductDataGrid extends DataGrid of ProductDataGrid.php:
comment ->leftJoin('attribute_families'...
comment ->leftJoin('product_inventories'...

edit ->select('product_flat.product_id as product_id'... to

->select('product_flat.product_id as product_id', 'product_flat.sku as product_sku', 'product_flat.name as product_name', 'product_flat.status', 'product_flat.price')

rahul

Hello @george

We gonna apply check on itemPerPage, which can not be more than 100, because due to this it is taking more time.

Thanks

rahul

Hello

You can apply it from configuration - https://prnt.sc/pphf6i

Thanks

george

Hello, Rahul, it's not helped.
Problems on big catalog (over 300 000 products):

"main page" loading about 10sec.
"/categories/xxxx" not working (blank screen, infinite loading)
admin panel, products: loading 5-10 sec
product form: 3-5sec.
Partially has fixed some code, but need some help.

Raw SQL queries with "default items per page" set to 1 or 99 is equally slow.
There are needed some optimization, adding indexes, simplify the queries, and etc.
Could we discuss this problem directly? Then you may add to your marketing phrase "big data" or something.

rahul

Hi @george

Can you send me dump of your database so that we can check and optimise it, because it is a big concern.

Thanks

george

This post is deleted!

rahul

Hi @george

I advice you to generate a ticket for same at https://bagisto.uvdesk.com/en/customer/create-ticket/

Thanks