Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I am running magento community edition and its db performance is horrible. I have the following query:

SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM `mage_catalog_product_flat_1` AS `e`   
 INNER JOIN `mage_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(2, 4) AND cat_index.category_id='2'                                                 
 INNER JOIN `mage_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 `mage_catalog_product_index_eav` AS `gender_idx` ON gender_idx.entity_id = e.entity_id AND gender_idx.attribute_id = '154' AND gender_idx.store_id = 1 AND gender_idx.value IN('70')                                              
 LEFT JOIN `mage_review_entity_summary` AS `at_rating_summary` ON (at_rating_summary.`entity_pk_value`=e.entity_id) AND
 (at_rating_summary.entity_type = '1') AND (at_rating_summary.store_id = '1')                                          
 INNER JOIN `mage_catalog_product_index_eav` AS `width_idx` ON width_idx.entity_id = e.entity_id AND width_idx.attribute_id = '153' AND width_idx.store_id = '1' GROUP BY `width_idx`.`value`;

The table *mage_catalog_product_flat_1* has 211931 rows in it. Here is EXPLAIN output:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: width_idx
         type: ref
possible_keys: PRIMARY,IDX_MAGE_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_MAGE_CATALOG_PRODUCT_IN
_MAGE_CATALOG_PRODUCT_INDEX_EAV_STORE_ID
          key: IDX_MAGE_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID
      key_len: 2
          ref: const
         rows: 18746
        Extra: Using where; Using index; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat_index
         type: eq_ref
possible_keys: PRIMARY,IDX_MAGE_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,18A130F8AA1
          key: PRIMARY
      key_len: 10
          ref: const,shoestore_stg.width_idx.entity_id,const
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: shoestore_stg.cat_index.product_id
         rows: 1
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: at_rating_summary
         type: ref
possible_keys: IDX_MAGE_REVIEW_ENTITY_SUMMARY_STORE_ID
          key: IDX_MAGE_REVIEW_ENTITY_SUMMARY_STORE_ID
      key_len: 2
          ref: const
         rows: 603
        Extra: 
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: gender_idx
         type: eq_ref
possible_keys: PRIMARY,IDX_MAGE_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_MAGE_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX
_MAGE_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_MAGE_CATALOG_PRODUCT_INDEX_EAV_VALUE
          key: PRIMARY
      key_len: 12
          ref: shoestore_stg.e.entity_id,const,const,const
         rows: 1
        Extra: Using where; Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: price_index
         type: eq_ref
possible_keys: PRIMARY,IDX_MAGE_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_MAGE_CATALOG_PRODUCT_INDEX_PRICE_WEBS
ITE_ID,IDX_MAGE_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
          key: PRIMARY
      key_len: 8
          ref: shoestore_stg.width_idx.entity_id,const,const
         rows: 1
        Extra: Using index

The slow query log has this query running over a minute:

# Query_time: 66.226261  Lock_time: 0.000433 Rows_sent: 44  Rows_examined: 34271886

How can I speed this query up ?

share|improve this question
my.cnf variables: innodb_lock_wait_timeout=300 innodb_buffer_pool_size=6G innodb_thread_concurrency=14 key_buffer_size=1024M max_allowed_packet=64M sort_buffer_size=8M read_buffer_size=8M read_rnd_buffer_size=4M myisam_sort_buffer_size=4M tmp_table_size=256M thread_stack=256K thread_cache_size=32 query_cache_limit=4M query_cache_size=256M query_cache_type=1 table_cache=1024 thread_cache_size=8 max_connections=400 wait_timeout=300 expire_logs_days=10 max_binlog_size=100M – Ketema May 22 at 2:36
mysql is on a RackSpace virtual server with their ssd attached storage. Mysql data dir is on the ssd disk. The server has 8GB of RAM and 6 cores. – Ketema May 22 at 2:36
Is it possible to switch to a bigger machine? how many products/store views/visitors do you have? – simonthesorcerer Jul 13 at 12:25
we wound up switching to RDS on amazon. We tweaked the same above variables on RDS and got marked improvement with actually LESS indicated resources than what we were using on RS. We also traced the query back to a module and removed the module. The store sped up significantly. – Ketema Jul 16 at 3:49
Good for you :) Can you tell us which module it was to avoid it in future? And maybe you would like to contact the developer to give him the chance of improvement – simonthesorcerer Jul 16 at 9:36
add comment (requires an account with 50 reputation)

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.