Large datasets: 1.5 million products (or more)

Bart

Hi,

I am testing a setup of bagisto with 1.5 million products to see if it can handle a large amount of products.

I noticed it takes over 2 seconds for a category page to load with just 9 products on it. After some fiddling around I discovered the query used is slowed down by the group by `product_flat`.`id` at the end of the query.

When removing the group by the query takes 0.025 on average and when adding the group by the query takes 0.45 on average.

The product flat query:

select distinct `product_flat`.* from `product_flat`
left join `products` on `product_flat`.`product_id` = `products`.`id`
left join `product_categories` on `products`.`id` = `product_categories`.`product_id` 
left join `product_flat` as `flat_variants` on `product_flat`.`id` = `flat_variants`.`parent_id` and `flat_variants`.`channel` = 'default' and `flat_variants`.`locale` = 'en'
left join `products` as `variants` on `products`.`id` = `variants`.`parent_id` 
where `product_flat`.`channel` = 'default' and `product_flat`.`locale` = 'en' and `product_flat`.`url_key` is not null and `product_categories`.`category_id` = '44' and `product_flat`.`status` = 1 and `product_flat`.`visible_individually` = 1 
group by `product_flat`.`id`
limit 9 offset 0;

Another thing I noticed was when opening a product page it took 5 seconds. This was due to the fact it uses "url_key" to select the product but there was no index on "url_key". Adding that index solved the problem.

rahul

Hi @Bart

Thanks for your concern, we will check and implement solution shortly.