In a shop we are maintaining it takes ages before the price indexes are up to date. While analyzing the process I've noticed that the bundle price indexer is using a query that takes 40 seconds to execute, which is way too long if you ask me. This query will be repeated for about 8 times so no wonder why the indexing problem is so slow.
Is there something I can do about it. If analyzed the query via Explain and that gave no optimizations that could be made. Are there any other options? I'd love to hear them.
The query looks like this:
SELECT `e`.`entity_id`,
`cg`.`customer_group_id`,
`cw`.`website_id`,
IF(IF(Ifnull(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value,
tad_tax_class_id.value) IS NOT NULL, IF(
Ifnull(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value,
tad_tax_class_id.value), 0)
AS
`tax_class_id`,
1
AS `price_type`,
IF(IF(ta_special_from_date.value IS NULL, 1, IF(
ta_special_from_date.value <= cwd.website_date, 1, 0)) > 0
AND IF(ta_special_to_date.value IS NULL, 1, IF(
ta_special_to_date.value >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) < 100, IF(
Ifnull(tas_special_price.value_id, -1) > 0, tas_special_price.value,
tad_special_price.value), 0)
AS
`special_price`,
tp.min_price
AS `tier_percent`,
IF(IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value,
tad_price.value) IS
NULL, 0, IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value,
tad_price.value))
AS `orig_price`,
IF(IF(IF(ta_special_from_date.value IS NULL, 1, IF(
ta_special_from_date.value <= cwd.website_date, 1, 0)) > 0
AND IF(ta_special_to_date.value IS NULL, 1, IF(
ta_special_to_date.value >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) < 100,
IF(
Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value), 0
) > 0,
Round(
IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)
* ( IF(
IF
(ta_special_from_date.value IS NULL, 1, IF(
ta_special_from_date.value <=
cwd.website_date, 1, 0)) > 0
AND IF(ta_special_to_date.value IS NULL, 1, IF(
ta_special_to_date.value >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) < 100,
IF(
Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value), 0
) / 100
), 4), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value,
tad_price.value))
AS `price`,
IF(IF(IF(ta_special_from_date.value IS NULL, 1, IF(
ta_special_from_date.value <= cwd.website_date, 1, 0)) > 0
AND IF(ta_special_to_date.value IS NULL, 1, IF(
ta_special_to_date.value >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) < 100,
IF(
Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value), 0
) > 0,
Round(
IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)
* ( IF(
IF
(ta_special_from_date.value IS NULL, 1, IF(
ta_special_from_date.value <=
cwd.website_date, 1, 0)) > 0
AND IF(ta_special_to_date.value IS NULL, 1, IF(
ta_special_to_date.value >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) < 100,
IF(
Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value), 0
) / 100
), 4), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value,
tad_price.value))
AS `min_price`,
IF(IF(IF(ta_special_from_date.value IS NULL, 1, IF(
ta_special_from_date.value <= cwd.website_date, 1, 0)) > 0
AND IF(ta_special_to_date.value IS NULL, 1, IF(
ta_special_to_date.value >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) < 100,
IF(
Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value), 0
) > 0,
Round(
IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)
* ( IF(
IF
(ta_special_from_date.value IS NULL, 1, IF(
ta_special_from_date.value <=
cwd.website_date, 1, 0)) > 0
AND IF(ta_special_to_date.value IS NULL, 1, IF(
ta_special_to_date.value >= cwd.website_date, 1, 0)) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) > 0
AND IF(Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value) < 100,
IF(
Ifnull(tas_special_price.value_id, -1) > 0,
tas_special_price.value,
tad_special_price.value), 0
) / 100
), 4), IF(Ifnull(tas_price.value_id, -1) > 0, tas_price.value,
tad_price.value))
AS `max_price`,
IF(tp.min_price IS NOT NULL, Round(IF(Ifnull(tas_price.value_id, -1) > 0,
tas_price.value, tad_price.value)
- (
IF(Ifnull(tas_price.value_id, -1) > 0,
tas_price.value, tad_price.value) * (
tp.min_price / 100 ) ), 4), NULL)
AS
`tier_price`,
IF(tp.min_price IS NOT NULL, Round(IF(Ifnull(tas_price.value_id, -1) > 0,
tas_price.value, tad_price.value)
- (
IF(Ifnull(tas_price.value_id, -1) > 0,
tas_price.value, tad_price.value) * (
tp.min_price / 100 ) ), 4), NULL)
AS
`base_tier`
FROM `catalog_product_entity` AS `e`
CROSS JOIN `customer_group` AS `cg`
CROSS JOIN `core_website` AS `cw`
INNER JOIN `core_store_group` AS `csg`
ON csg.group_id = cw.default_group_id
INNER JOIN `core_store` AS `cs`
ON cs.store_id = csg.default_store_id
INNER JOIN `catalog_product_website` AS `pw`
ON pw.product_id = e.entity_id
AND pw.website_id = cw.website_id
INNER JOIN `catalog_product_index_website` AS `cwd`
ON cw.website_id = cwd.website_id
LEFT JOIN `catalog_product_index_tier_price` AS `tp`
ON tp.entity_id = e.entity_id
AND tp.website_id = cw.website_id
AND tp.customer_group_id = cg.customer_group_id
INNER JOIN `catalog_product_entity_int` AS `tad_status`
ON tad_status.entity_id = e.entity_id
AND tad_status.attribute_id = 80
AND tad_status.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_status`
ON tas_status.entity_id = e.entity_id
AND tas_status.attribute_id = 80
AND tas_status.store_id = cs.store_id
LEFT JOIN `catalog_product_entity_int` AS `tad_tax_class_id`
ON tad_tax_class_id.entity_id = e.entity_id
AND tad_tax_class_id.attribute_id = 81
AND tad_tax_class_id.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_tax_class_id`
ON tas_tax_class_id.entity_id = e.entity_id
AND tas_tax_class_id.attribute_id = 81
AND tas_tax_class_id.store_id = cs.store_id
INNER JOIN `catalog_product_entity_int` AS `ta_price_type`
ON ta_price_type.entity_id = e.entity_id
AND ta_price_type.attribute_id = 470
AND ta_price_type.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `tad_price`
ON tad_price.entity_id = e.entity_id
AND tad_price.attribute_id = 60
AND tad_price.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `tas_price`
ON tas_price.entity_id = e.entity_id
AND tas_price.attribute_id = 60
AND tas_price.store_id = cs.store_id
LEFT JOIN `catalog_product_entity_decimal` AS `tad_special_price`
ON tad_special_price.entity_id = e.entity_id
AND tad_special_price.attribute_id = 61
AND tad_special_price.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `tas_special_price`
ON tas_special_price.entity_id = e.entity_id
AND tas_special_price.attribute_id = 61
AND tas_special_price.store_id = cs.store_id
LEFT JOIN `catalog_product_entity_datetime` AS `ta_special_from_date`
ON ta_special_from_date.entity_id = e.entity_id
AND ta_special_from_date.attribute_id = 62
AND ta_special_from_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `ta_special_to_date`
ON ta_special_to_date.entity_id = e.entity_id
AND ta_special_to_date.attribute_id = 63
AND ta_special_to_date.store_id = 0
INNER JOIN `cataloginventory_stock_status` AS `ciss`
ON ciss.product_id = e.entity_id
AND ciss.website_id = cw.website_id
WHERE ( e.type_id = 'bundle' )
AND ( IF(Ifnull(tas_status.value_id, -1) > 0, tas_status.value,
tad_status.value) = 1 )
AND ( ta_price_type.value = 1 )
AND ( e.entity_id IN( 99, 228, 248, 249,
251, 252, 257, 263, 275 ) )
AND ( ciss.stock_status = 1 )
Here is a thread that explains how to reduce that specific query. I havent spent time trying it my self, when i do i would look to make the fix as a module, but the quicker way to do it is by placing a copy of the file in the 'code/local' folder.
http://www.magentocommerce.com/boards/viewthread/38818/P90/#t367525