Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

query takes long time to run
 
lunja
Jr. Member
 
Avatar
Total Posts:  12
Joined:  2011-06-13
 

Hello All,

This query is used in the sitemap functions but it takes 286 seconds to complete.

Any suggestions how i can make it any faster:

SELECT `e`.`entity_id`, `ur`.`request_path` AS `urlFROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_product_website` AS `wON e.entity_id=w.product_id
 LEFT JOIN 
`core_url_rewrite` AS `urON e.entity_id=ur.product_id AND ur.category_id IS NULL AND ur.store_id='1' AND ur.is_system=1
 INNER JOIN 
`catalog_product_entity_int` AS `t1_visibilityON e.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0
 LEFT JOIN 
`catalog_product_entity_int` AS `t2_visibilityON t1_visibility.entity_id t2_visibility.entity_id AND t1_visibility.attribute_id t2_visibility.attribute_id AND t2_visibility.store_id='1'
 
INNER JOIN `catalog_product_entity_int` AS `t1_statusON e.entity_id=t1_status.entity_id AND t1_status.store_id=0
 LEFT JOIN 
`catalog_product_entity_int` AS `t2_statusON t1_status.entity_id t2_status.entity_id AND t1_status.attribute_id t2_status.attribute_id AND t2_status.store_id='1' WHERE (w.website_id='1') AND (t1_visibility.attribute_id='102') AND ((IF(t2_visibility.value_id 0t2_visibility.valuet1_visibility.value)) IN(324)) AND (t1_status.attribute_id='96') AND ((IF(t2_status.value_id 0t2_status.valuet1_status.value)) IN(1))
AFF1097
TIME
286.0470
 
Magento Community Magento Community
Magento Community
Magento Community
 
aarpol
Jr. Member
 
Total Posts:  13
Joined:  2010-02-17
 

I had an identical query running for a minute at a time on a product page. It turned out to be caused not by the sitemap, but by the Yoast Canonical extension. Maybe you have that installed?

 
Magento Community Magento Community
Magento Community
Magento Community
 
lunja
Jr. Member
 
Avatar
Total Posts:  12
Joined:  2011-06-13
 

eventually the problem was getting the URL from the core_url_rewrite table this table almost had about an million records. After cleaning old URLs now it works proberly

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top