Bagisto Forum

    Bagisto

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups

    1 Million products!

    General Discussion
    2
    12
    1213
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • G
      george last edited by

      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.

      1 Reply Last reply Reply Quote 0
      • G
        george last edited by

        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.

        1 Reply Last reply Reply Quote 0
        • G
          george last edited by

          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.

          1 Reply Last reply Reply Quote 0
          • G
            george last edited by 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')
            
            1 Reply Last reply Reply Quote 0
            • R
              rahul last edited by

              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

              1 Reply Last reply Reply Quote 0
              • R
                rahul last edited by

                Hello

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

                Thanks

                1 Reply Last reply Reply Quote 0
                • G
                  george last edited by

                  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.

                  1 Reply Last reply Reply Quote 0
                  • R
                    rahul last edited by

                    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

                    1 Reply Last reply Reply Quote 1
                    • G
                      george last edited by

                      This post is deleted!
                      1 Reply Last reply Reply Quote 0
                      • R
                        rahul last edited by

                        Hi @george

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

                        Thanks

                        1 Reply Last reply Reply Quote 0
                        • First post
                          Last post