search

Vinai / groupscatalog2

Magento extension to enable you to hide categories and products from customers depending on their customer group. This is a Magento 1.6 and newer compatible version of the Netzarbeiter Customer Groups Catalog extension.
139 stars 59 forks source link

Slow Query Log issue #106

Closed moneythakur closed 9 years ago

moneythakur commented 9 years ago

I have installed this extension and I am getting this extension query in slow query log with row examined about 20 lacs. how to get out of this trouble

Vinai commented 9 years ago

In order to help I need more information. How have you installed the extension and which version are you using? Also, which version of Magento are you using? How many products, categories, customer groups and store views are present in your Magento instance? Please also post the SQL query in question.

tniebergall commented 9 years ago

Hi Vinai,

i have some query issues too.

Magento: 1.7.0.2 Products: 2737 Categories: 260 Customer Groups: 6 Storeviews: 2

This query runs several times and takes a mass of resources. Could it be a bug of the extension or is it a miss configuration? We took this store over from an other agency this week. They installed the extension via the downloader in Version 0.2.4 beta. Could an Update solve this issue?

SELECT count_table.category_id, COUNT(DISTINCT count_table.product_id) AS product_count FROM catalog_product_flat_1 AS e INNER JOIN catalogsearch_result AS search_result ON search_result.product_id=e.entity_id AND search_result.query_id='141' INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id='2' INNER JOIN groupscatalog_product_idx ON e.entity_id=groupscatalog_product_idx.entity_id AND groupscatalog_product_idx.group_id=0 AND groupscatalog_product_idx.store_id=1 INNER JOIN catalog_category_product_index AS count_table ON count_table.product_id = e.entity_id INNER JOIN groupscatalog_product_idx AS groupscatalog_product_idx_2 ON groupscatalog_product_idx.entity_id=e.entity_id AND groupscatalog_product_idx.group_id=0 AND groupscatalog_product_idx.store_id=1 WHERE (count_table.store_id = 1) AND (count_table.category_id IN ('73', '3', '11', '19', '26', '33', '47', '54', '62')) GROUP BY count_table.category_id

Thanks a lot. Please let me know if you need some more informations.

Best regards Tobias

Vinai commented 9 years ago

Hi, I'm not sure if an update would help, but it should not even be noticable with such a relatively small catalog. I definitely reommend to upgrade to the latest version 0.3.6.

tniebergall commented 9 years ago

Hi,

thanks i will do an upgrade an check the configuration.

tniebergall commented 9 years ago

Hi,

the update solved the problem.

Thanks again