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

Page 1 of 2
This query is killing my mysql server
 
maciek79
Jr. Member
 
Total Posts:  15
Joined:  2007-11-20
 

Hello,
Today I received an information from my hosting company that this query below is very resource consuming:
Maybe this would be some kind of info for Magento developers.

SELECT DISTINCT  `e` . * ,  `_table_position`.`position` ,  `_table_store_id`.`store_id` ,  `_table_status`.`value` AS `status` ,  `_table_visibility`.`value` AS `visibility` ,  `_table_category_12`.`position` AS `category_12` ,  `_table_price`.`value` AS `price` ,  `_table_manufacturer`.`value` AS `manufacturer` ,  `_table_aparat_matrix`.`value` AS `aparat_matrix` ,  `_table_aparat_lcd_size`.`value` AS `aparat_lcd_size` ,  `_table_pamiec_rodzaj`.`value` AS `pamiec_rodzaj` , COUNTDISTINCT e.entity_id ) AS `count_zoom_optyczny` , zoom_optyczny_value_count.value AS `value_zoom_optyczny`
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product` AS `_table_positionON _table_position.product_id e.entity_id )
AND (
_table_position.category_id
IN 
(
'4''12''11'
)
)
INNER JOIN `catalog_product_store` AS `_table_store_idON _table_store_id.product_id e.entity_id )
AND (
_table_store_id.store_id =1
)
INNER JOIN `catalog_product_entity_int` AS `_table_statusON _table_status.entity_id e.entity_id )
AND (
_table_status.store_id =1
)
AND (
_table_status.attribute_id '273'
)
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON _table_visibility.entity_id e.entity_id )
AND (
_table_visibility.store_id =1
)
AND (
_table_visibility.attribute_id '526'
)
INNER JOIN `catalog_category_product` AS `_table_category_12ON _table_category_12.product_id e.entity_id )
AND (
_table_category_12.category_id
IN 
(
'12'
)
)
INNER JOIN `catalog_product_entity_decimal` AS `_table_priceON _table_price.entity_id e.entity_id )
AND (
_table_price.store_id =1
)
AND (
_table_price.attribute_id '99'
)
INNER JOIN `catalog_product_entity_varchar` AS `_table_manufacturerON _table_manufacturer.entity_id e.entity_id )
AND (
_table_manufacturer.store_id =1
)
AND (
_table_manufacturer.attribute_id '102'
)
INNER JOIN `catalog_product_entity_text` AS `_table_aparat_matrixON _table_aparat_matrix.entity_id e.entity_id )
AND (
_table_aparat_matrix.store_id =1
)
AND (
_table_aparat_matrix.attribute_id '536'
)
INNER JOIN `catalog_product_entity_text` AS `_table_aparat_lcd_sizeON _table_aparat_lcd_size.entity_id e.entity_id )
AND (
_table_aparat_lcd_size.store_id =1
)
AND (
_table_aparat_lcd_size.attribute_id '537'
)
INNER JOIN `catalog_product_entity_int` AS `_table_pamiec_rodzajON _table_pamiec_rodzaj.entity_id e.entity_id )
AND (
_table_pamiec_rodzaj.store_id =1
)
AND (
_table_pamiec_rodzaj.attribute_id '539'
)
INNER JOIN `catalog_product_entity_text` AS `zoom_optyczny_value_countON e.entity_id zoom_optyczny_value_count.entity_id
AND zoom_optyczny_value_count.attribute_id '535'
AND zoom_optyczny_value_count.store_id =1
WHERE 
(
e.entity_type_id =10
)
AND (
_table_status.value
IN 
1)
)
AND (
_table_visibility.value
IN 
2)
)
AND (
_table_price.value >=780
AND _table_price.value <= 789.999
)
AND (
_table_manufacturer.value '105'
)
AND (
_table_aparat_matrix.value '147'
)
AND (
_table_aparat_lcd_size.value '152'
)
AND (
_table_pamiec_rodzaj.value '131'
)
GROUP BY `value_zoom_optyczny`
 
Magento Community Magento Community
Magento Community
Magento Community
 
Mark_Kimsal
Sr. Member
 
Total Posts:  186
Joined:  2007-09-12
Michigan, USA
 

Good Gravy that’s huge.

Thanks for the info, that might help some Magento people streamline that operation.  Unfortunately, most of the SQL is automatically created by function calls, so it’s a bit difficult to simply streamline this exact SQL.

With that being said… it executes in 0.11 seconds on my setup.  Adding “EXPLAIN” to the front of the SQL shows that, at most, it’s scanning only 13 records.  This is on mysql 5.0.45.  Perhaps they are using a version of MySQL that would have problems with this kind of query.  What version of MySQL is being used in your situation?

(note: my setup is a single-user workstation, not a shared server of any sort.)

 
Magento Community Magento Community
Magento Community
Magento Community
 
maciek79
Jr. Member
 
Total Posts:  15
Joined:  2007-11-20
 

just to add some details:
Mysql version: 5.0.45
Magento: 0.7.14800

I have about 100 products (each with about 2-7 attributes). While browsing I tried to narrow visible products selecting some attribute values in layered navigation box. After few clicks I get such a query.

Maciek

 
Magento Community Magento Community
Magento Community
Magento Community
 
winans_fred
Sr. Member
 
Total Posts:  83
Joined:  2007-09-06
 

can we say “subquery”?

 
Magento Community Magento Community
Magento Community
Magento Community
 
diri
Member
 
Total Posts:  48
Joined:  2008-01-13
 

Devs might try to not to use “SELECT *” in such a JOINed query. I saw speed ups in range of factor 400 and more when getting rid of such unspecified SQL with several applications (and servers).

 
Magento Community Magento Community
Magento Community
Magento Community
 
Garri
Jr. Member
 
Avatar
Total Posts:  30
Joined:  2007-09-18
 

DISTINCT should be used when there is no way to do without it. It depends a lot of performance.
So if you need to add indexes where they do not exist.
P.S.
Easily you can find other heavy requests when they add to mysql config log_slow_queries and long_query_time options.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Daim
Sr. Member
 
Total Posts:  172
Joined:  2008-01-08
Germany
 

I found two more heavy queries.Whe habe 60.000 Products in the database in only 4 Categories. When i search a product i must wait 40-60 sec.

# Query_time: 19  Lock_time: 0  Rows_sent: 1  Rows_examined: 3747619532
select count(DISTINCT e.entity_idfrom `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_product_entity_int` AS `_table_statusON (_table_status.entity_id e.entity_id) AND (_table_status.store_id=1) AND (_table_status.attribute_id='273')
 
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON (_table_visibility.entity_id e.entity_id) AND (_table_visibility.store_id=1) AND (_table_visibility.attribute_id='526')
WHERE
    
(e.entity_type_id 10)
    AND (
e.entity_id in (SELECT
    
`catalog_product_entity_text`.`entity_id`
FROM `catalog_product_entity_text`
WHERE
    
(store_id=1)
    AND (
attribute_id IN ('559''561''562''563''611''607''610''97''613''614''612''603''506'))
    AND (
value LIKE '&#xLF;-MA0014%'UNION SELECT
    
`catalog_product_entity_varchar`.`entity_id`
FROM `catalog_product_entity_varchar`
WHERE
    
(store_id=1)
    AND (
attribute_id IN ('102''96'))
    AND (
value LIKE '&#xLF;-MA0014%')))
    AND (
_table_status.value in (13))
    AND (
_table_visibility.value in (34));



# Query_time: 19  Lock_time: 0  Rows_sent: 1  Rows_examined: 3747619536
SELECT
    
`e`.*,
    `
_table_status`.`value` AS `status`,
    `
_table_visibility`.`value` AS `visibility`,
    `
_table_name`.`value` AS `name`
FROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_product_entity_int` AS `_table_statusON (_table_status.entity_id e.entity_id) AND (_table_status.store_id=1) AND (_table_status.attribute_id='273')
 
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON (_table_visibility.entity_id e.entity_id) AND (_table_visibility.store_id=1) AND (_table_visibility.attribute_id='526')
 
INNER JOIN `catalog_product_entity_varchar` AS `_table_nameON (_table_name.entity_id e.entity_id) AND (_table_name.store_id=1) AND (_table_name.attribute_id='96')
WHERE
    
(e.entity_type_id 10)
    AND (
e.entity_id in (SELECT
    
`catalog_product_entity_text`.`entity_id`
FROM `catalog_product_entity_text`
WHERE
    
(store_id=1)
    AND (
attribute_id IN ('559''561''562''563''611''607''610''97''613''614''612''603''506'))
    AND (
value LIKE '&#xLF;-MA0014%'UNION SELECT
    
`catalog_product_entity_varchar`.`entity_id`
FROM `catalog_product_entity_varchar`
WHERE
    
(store_id=1)
    AND (
attribute_id IN ('102''96'))
    AND (
value LIKE '&#xLF;-MA0014%')))
    AND (
_table_status.value in (13))
    AND (
_table_visibility.value in (34))
ORDER BY
    
`_table_name`.`valueasc
 LIMIT 5
;
 
Magento Community Magento Community
Magento Community
Magento Community
 
Garri
Jr. Member
 
Avatar
Total Posts:  30
Joined:  2007-09-18
 

@Daim
and do you know where your data query, in which files? simply I do not have the time for search smile I would have tried to optimization. Can I take these 60000 products to the test? Just database.
although, it’s silly of course, who could let own database..

 
Magento Community Magento Community
Magento Community
Magento Community
 
Daim
Sr. Member
 
Total Posts:  172
Joined:  2008-01-08
Germany
 

@Garri

Sorry but i can’t give you my database because his contains data from my company. But you can easy generate 60.000 Products.

@Magento-Team

Do your work on this Problem (Product Search) or is this Module for the team finished?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Michael_1
Enthusiast
 
Total Posts:  826
Joined:  2007-08-31
 
Daim - 24 January 2008 05:18 AM

@Magento-Team

Do your work on this Problem (Product Search) or is this Module for the team finished?

Hi Daim,

We are still working on it in order to optimize Magento performance. We’ll update the community on its progress soon.

Please let me know if you have any other performance issues on your database.

Thank you,
Michael.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Daim
Sr. Member
 
Total Posts:  172
Joined:  2008-01-08
Germany
 

Thanks for your answer Michael.

At the moment we import 1036 categories in magento and link this with products. I am advertised to community alpha tester but i did’t become a answer.
Also i dont’t have access to the svn truck :(

 
Magento Community Magento Community
Magento Community
Magento Community
 
Daim
Sr. Member
 
Total Posts:  172
Joined:  2008-01-08
Germany
 

Hello Michael ,

i found more queries :(
But at first all tables now MyISAM? http://www.magentocommerce.com/boards/viewthread/3010/

Now i have 80.000 Products in the database. All Products are in one categories because we don’t have time now to map all products to the catagories.
And we have 1756 categories and 10 Attributes (Filter with Result) and I set getStoreCategories from 10 to 1.
Now if i click i the categorie i can go and make a tea (99.79549 sec.) This is really bad. Her the queries if you have more questions PM or Email.

# Query_time: 14  Lock_time: 0  Rows_sent: 4  Rows_examined: 633715
use magento2;
SELECT DISTINCT
        
`set_distinct`.`attribute_set_id`
FROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_category_product` AS `_table_positionON (_table_position.product_id=e.entity_id) AND (_table_position.category_id IN ('3''4''23''28''37''78''100''127''140'))
 
INNER JOIN `catalog_product_store` AS `_table_store_idON (_table_store_id.product_id=e.entity_id) AND (_table_store_id.store_id=1)
 
INNER JOIN `catalog_product_entity_int` AS `_table_statusON (_table_status.entity_id e.entity_id) AND (_table_status.store_id='1') AND (_table_status.attribute_id='64')
 
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON (_table_visibility.entity_id e.entity_id) AND (_table_visibility.store_id='1') AND (_table_visibility.attribute_id='69')
 
INNER JOIN `catalog_product_entity` AS `set_distinctON e.entity_id=set_distinct.entity_id
WHERE
        
(e.entity_type_id 5)
        AND (
_table_status.value in (13))
        AND (
_table_visibility.value in (24));
# Time: 080201  8:24:13
# User@Host: root[root] @ localhost []
# Query_time: 15  Lock_time: 0  Rows_sent: 1  Rows_examined: 712584
SELECT DISTINCT
        
`e`.*,
        `
_table_position`.`position`,
        `
_table_store_id`.`store_id`,
        `
_table_status`.`value` AS `status`,
        `
_table_visibility`.`value` AS `visibility`,
        
MAX(price_max_value.value) AS `max_price`
FROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_category_product` AS `_table_positionON (_table_position.product_id=e.entity_id) AND (_table_position.category_id IN ('3''4''23''28''37''78''100''127''140'))
 
INNER JOIN `catalog_product_store` AS `_table_store_idON (_table_store_id.product_id=e.entity_id) AND (_table_store_id.store_id=1)
 
INNER JOIN `catalog_product_entity_int` AS `_table_statusON (_table_status.entity_id e.entity_id) AND (_table_status.store_id='1') AND (_table_status.attribute_id='64')
 
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON (_table_visibility.entity_id e.entity_id) AND (_table_visibility.store_id='1') AND (_table_visibility.attribute_id='69')
 
INNER JOIN `catalog_product_entity_decimal` AS `price_max_valueON e.entity_id=price_max_value.entity_id
            
AND price_max_value.attribute_id '47'
            
AND price_max_value.store_id '1'
WHERE
        
(e.entity_type_id 5)
        AND (
_table_status.value in (13))
        AND (
_table_visibility.value in (24))
GROUP BY
        
`e`.`entity_type_id`;
 
Magento Community Magento Community
Magento Community
Magento Community
 
Daim
Sr. Member
 
Total Posts:  172
Joined:  2008-01-08
Germany
 

# Query_time: 9  Lock_time: 0  Rows_sent: 8  Rows_examined: 873055
SELECT DISTINCT
        
`e`.*,
        `
_table_position`.`position`,
        `
_table_store_id`.`store_id`,
        `
_table_status`.`value` AS `status`,
        `
_table_visibility`.`value` AS `visibility`,
        
COUNT(DISTINCT e.entity_id) AS `count_price`,
        
CEIL((price_range_count_value.value+0.01)/1000) AS `range_price`
FROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_category_product` AS `_table_positionON (_table_position.product_id=e.entity_id) AND (_table_position.category_id IN ('3''4''23''28''37''78''100''127''140'))
 
INNER JOIN `catalog_product_store` AS `_table_store_idON (_table_store_id.product_id=e.entity_id) AND (_table_store_id.store_id=1)
 
INNER JOIN `catalog_product_entity_int` AS `_table_statusON (_table_status.entity_id e.entity_id) AND (_table_status.store_id='1') AND (_table_status.attribute_id='64')
 
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON (_table_visibility.entity_id e.entity_id) AND (_table_visibility.store_id='1') AND (_table_visibility.attribute_id='69')
 
INNER JOIN `catalog_product_entity_decimal` AS `price_range_count_valueON e.entity_id=price_range_count_value.entity_id
            
AND price_range_count_value.attribute_id '47'
            
AND price_range_count_value.store_id '1'
WHERE
        
(e.entity_type_id 5)
        AND (
_table_status.value in (13))
        AND (
_table_visibility.value in (24))
GROUP BY
        
`range_price`;
SELECT DISTINCT
        
`e`.*,
        `
_table_position`.`position`,
        `
_table_store_id`.`store_id`,
        `
_table_status`.`value` AS `status`,
        `
_table_visibility`.`value` AS `visibility`,
        
COUNT(DISTINCT e.entity_id) AS `count_ab_ag_reactivity`,
        
ab_ag_reactivity_value_count.value AS `value_ab_ag_reactivity`
FROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_category_product` AS `_table_positionON (_table_position.product_id=e.entity_id) AND (_table_position.category_id IN ('3''4''23''28''37''78''100''127''140'))
 
INNER JOIN `catalog_product_store` AS `_table_store_idON (_table_store_id.product_id=e.entity_id) AND (_table_store_id.store_id=1)
 
INNER JOIN `catalog_product_entity_int` AS `_table_statusON (_table_status.entity_id e.entity_id) AND (_table_status.store_id='1') AND (_table_status.attribute_id='64')
 
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON (_table_visibility.entity_id e.entity_id) AND (_table_visibility.store_id='1') AND (_table_visibility.attribute_id='69')
 
INNER JOIN `catalog_product_entity_text` AS `ab_ag_reactivity_value_countON e.entity_id=ab_ag_reactivity_value_count.entity_id
            
AND ab_ag_reactivity_value_count.attribute_id '561'
            
AND ab_ag_reactivity_value_count.store_id '1'
WHERE
        
(e.entity_type_id 5)
        AND (
_table_status.value in (13))
        AND (
_table_visibility.value in (24))
GROUP BY
        
`value_ab_ag_reactivity`;
# Time: 080201  8:24:53
# User@Host: root[root] @ localhost []
# Query_time: 13  Lock_time: 0  Rows_sent: 1237  Rows_examined: 584752
SELECT DISTINCT
        
`e`.*,
        `
_table_position`.`position`,
        `
_table_store_id`.`store_id`,
        `
_table_status`.`value` AS `status`,
        `
_table_visibility`.`value` AS `visibility`,
        
COUNT(DISTINCT e.entity_id) AS `count_ab_applications`,
        
ab_applications_value_count.value AS `value_ab_applications`
FROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_category_product` AS `_table_positionON (_table_position.product_id=e.entity_id) AND (_table_position.category_id IN ('3''4''23''28''37''78''100''127''140'))
 
INNER JOIN `catalog_product_store` AS `_table_store_idON (_table_store_id.product_id=e.entity_id) AND (_table_store_id.store_id=1)
 
INNER JOIN `catalog_product_entity_int` AS `_table_statusON (_table_status.entity_id e.entity_id) AND (_table_status.store_id='1') AND (_table_status.attribute_id='64')
 
INNER JOIN `catalog_product_entity_int` AS `_table_visibilityON (_table_visibility.entity_id e.entity_id) AND (_table_visibility.store_id='1') AND (_table_visibility.attribute_id='69')
 
INNER JOIN `catalog_product_entity_text` AS `ab_applications_value_countON e.entity_id=ab_applications_value_count.entity_id
            
AND ab_applications_value_count.attribute_id '562'
            
AND ab_applications_value_count.store_id '1'
WHERE
        
(e.entity_type_id 5)
        AND (
_table_status.value in (13))
        AND (
_table_visibility.value in (24))
GROUP BY
        
`value_ab_applications`;

And 2-3 queries more :(

 
Magento Community Magento Community
Magento Community
Magento Community
 
Daim
Sr. Member
 
Total Posts:  172
Joined:  2008-01-08
Germany
 

@Magento-Team

It’s nobody helps when you did’nt answer.  I am wait 3 days for any reaction but nothing. You can’t conceal the Peformanceproblem.
Please give a small Feedback.

 
Magento Community Magento Community
Magento Community
Magento Community
 
RoyRubin
Enthusiast
 
Avatar
Total Posts:  968
Joined:  2007-08-07
Los Angeles, CA
 

Look above - I think someone from the Magento Team did answer you.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Garri
Jr. Member
 
Avatar
Total Posts:  30
Joined:  2007-09-18
 
Daim - 04 February 2008 04:31 AM

@Magento-Team

It’s nobody helps when you did’nt answer.  I am wait 3 days for any reaction but nothing. You can’t conceal the Peformanceproblem.
Please give a small Feedback.

I think that the guys from the Varien works a lot and of course they are aware of this issue and on other. If calculate how many people in community with question, becomes clear that this is very heavy giving answer for everyone.
More difficult is to answer for redundantly questions.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2