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

Tag queries performance problems
 
jsperri
Sr. Member
 
Total Posts:  126
Joined:  2007-08-31
Fistufle
 

I am performing some load testing with a few thousand of products added to the test shop, and I am experiencing some performance problems that seems to be related to the queries on tags. I tracked it down to the following SQL that take minutes to execute on a system loaded with a few thousand tags (there are 3 tags per product) :

SELECT
        
`main_table`.*,
        
COUNT(DISTINCT relation.tag_relation_id) AS `popularity`,
        `
relation`.*
FROM `tag` AS `main_table`
 
LEFT JOIN `tag_relation` AS `prelationON main_table.tag_id=prelation.tag_id
 LEFT JOIN 
`tag_relation` AS `relationON main_table.tag_id=relation.tag_id
 INNER JOIN 
`tag_summary` AS `summary_storeON main_table.tag_id summary_store.tag_id AND summary_store.store_id 1
WHERE
        
(main_table.status 1)
        AND (
relation.active 1)
        AND (
prelation.active 1)
        AND (
relation.store_id 1)
        AND (
prelation.store_id 1)
GROUP BY
        
`main_table`.`tag_id`
ORDER BY
        popularity DESC
 LIMIT 20
;

The thousands of tags were created programmatically, but there should be no problem in their structure as the system behaves correctly when there are just a few products/tags.

 
Magento Community Magento Community
Magento Community
Magento Community
 
YoavKutner
Guru
 
Avatar
Total Posts:  491
Joined:  2007-08-08
 

Thanks for noticing this. We are looking into it.

Thanks

yoav

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