CODEWITHSUNDEEP
mysqlmagentonginxdatabase-performance
Nataraj
Nataraj

Reputation: 942

query taking long time to execute and crashing the site

I am having around 2.5 lachs (250K) products and 2600 subcategories on magento application (community edition).

Query 

SELECT 1 status
     , e.entity_id
     , e.type_id
     , e.attribute_set_id
     , cat_index.position AS cat_index_position
     , e.name
     , e.description
     , e.short_description
     , e.price
     , e.special_price
     , e.special_from_date
     , e.special_to_date
     , e.cost
     , e.small_image
     , e.thumbnail
     , e.color
     , e.color_value
     , e.news_from_date
     , e.news_to_date
     , e.url_key
     , e.required_options
     , e.image_label
     , e.small_image_label
     , e.thumbnail_label
     , e.msrp_enabled
     , e.msrp_display_actual_price_type
     , e.msrp
     , e.tax_class_id
     , e.price_type
     , e.weight_type
     , e.price_view
     , e.shipment_type
     , e.links_purchased_separately
     , e.links_exist
     , e.open_amount_min
     , e.open_amount_max
     , e.custom_h1
     , e.awards
     , e.region
     , e.grape_type
     , e.food_match
     , e.udropship_vendor
     , e.upc_barcode
     , e.ean_barcode
     , e.mpn
     , e.size
     , e.author
     , e.format
     , e.pagination
     , e.publish_date
     , price_index.price
     , price_index.tax_class_id
     , price_index.final_price
     , IF(price_index.tier_price IS NOT NULL
     , LEAST(price_index.min_price
     , price_index.tier_price)
     , price_index.min_price) AS minimal_price
     , price_index.min_price
     , price_index.max_price
     , price_index.tier_price 
  FROM catalog_product_flat_1 e
  JOIN catalog_category_product_index 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 = 163
  JOIN catalog_product_index_price price_index 
    ON price_index.entity_id = e.entity_id 
   AND price_index.website_id = 1 
   AND price_index.customer_group_id = 0 
 GROUP 
    BY e.entity_id 
 ORDER 
    BY cat_index_position ASC
     , cat_index.position ASC 
 LIMIT 15;

whenever accessing any products on this magento site it created a huge data under /tmp directory on theserver which is around 10 GB.

How can I fix this please suggest some solution.

Database size is 50 GB and server is nginx.

Upvotes: 0

Views: 500

Answers (2)

rms_3792748
rms_3792748

Reputation: 3

Are you have sufficient Hardware resources to run a big query and also please update you hardware configuration of server.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108766

You are misusing GROUP BY. Please learn how it works. There's a misfeature in MySQL which allows you to misuse it. Unfortunately, queries that misuse it are very difficult to troubleshoot.

It is difficult to infer what you are trying to do from your query. When you're dealing with result sets of that size, it helps to know your intent.

You should know, if you don't already, that queries of the form

 SELECT <<many columns>>
   FROM large_table
   JOIN another_large_table ON something
   JOIN another_large_table ON something
  ORDER BY some_arbitrary_column
  LIMIT some_small_number

can be grossly inefficient because they have to generate an enormous result set, then sort the whole thing, then return the first results. The sort operation carries the whole result set with it. You could be instructing the MySQL server to sort a crore or two of rows (dozens of megarows).

It looks like you want the first fifteen results starting with the lowest cat_index.position value. Accordingly, you may be able to make your query faster by joining with an appropriate subset of the table you call cat_index, like so:

SELECT 1 status, many_other_columns
  FROM catalog_product_flat_1 e
  JOIN (   /* join only with fifteen lowest eligible position values in cat_index */
     SELECT * 
       FROM catalog_category_product_index
      WHERE store_id = 1 
        AND visibility IN(2,4) 
        AND category_id = 163
      ORDER BY position ASC
      LIMIT 15
       ) AS cat_index ON cat_index.product_id = e.entity_id 
  JOIN catalog_product_index_price price_index 
             ON price_index.entity_id = e.entity_id 
            AND price_index.website_id = 1 
            AND price_index.customer_group_id = 0 
 GROUP BY e.entity_id     /*wrong!!*/
 ORDER BY cat_index_position ASC,   /* redundant!*/
          cat_index.position ASC 
 LIMIT 15;

It's worth a try.

Upvotes: 5

Related Questions