Huge MySql Query processed from frontend

Question

We have several web servers and MySql Server. Once in a while there is a huge query that being requested from one of the web servers (which means it comes from frontend).

We have a full page cache enabled. With redis support. So this kind of query is very strange. A especially when it comes from frontend.

The query is too long and it can take up to 20 sec to complete. When it occurs on several servers in parallel it can break down the whole system and website will fall.

The query goes like this:

`SELECT t_d.entity_id, t_d.attribute_id, t_d.value AS default_value,t_s.value AS store_value, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS value FROMcatalog_product_entity_varchar AS t_d\n LEFT JOIN catalog_product_entity_varcharAS t_s ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 2 WHERE (t_d.entity_type_id = 4) AND (t_d.entity_id IN (2,000 of products id comes here))` 

,and this query is being joined 5 or 6 times. Each time with 2,000 products

How can I tell where does this query comes from? How can I disable this dynamic query or whatever it is??

Thanks in advance

• FlorinelChis: how many products do you have on your Magento store?
• Livne Berebi: 4k total. 2k are simple and the rest are configurable.
• parhamr: Could this query be for a sitemap?
• Livne Berebi: no. my sitemap is off. and this kind of query occurs when loading category (latest finding) . when the category is not cached of coures.

Answer 1

First, if you aren't running New Relic, you probably should. It can help provide data & insight.

This is likely coming from Mage_Catalog_Model_Resource_Collection_Abstract::_getLoadAttributesSelect(). This may be the result of a category load or a configurable product load.

• Livne Berebi: but does this kind of big query is normal? or there's something wrong out there...
• benmarks: These large queries are possible under certain not-rare circumstances. Based on your simple/configurable ratio, I'm wondering if you are not suffering from some inefficiencies in how configurable options are determined.