Consulta que Magento realiza para re-indexar los productos en la categorías anchor

reindex-anchor-category.sql

## _reindexNonAnchorCategories
## This method is not the problem
##
## Results
## Range self::RANGE_CATEGORY_STEP = 500
## Take the entities which id is between 500*n 500*(n+1)
## Benchmark completed 4m 21s 77ms X 32 queries = 2h 19m 12s = 2h
INSERT IGNORE INTO `catalog_category_product_index_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`)
  SELECT
    `cc`.`entity_id`               AS `category_id`,
    `ccp`.`product_id`,
    ccp.position + 10000           AS `position`,
    0                              AS `is_parent`,
    1                              AS `store_id`,
    IFNULL(cpvs.value, cpvd.value) AS `visibility`
  FROM `catalog_category_entity` AS `cc`
    INNER JOIN `catalog_category_entity` AS `cc2` ON cc2.path LIKE CONCAT(`cc`.`path`, '/%') AND cc.entity_id NOT IN (1)
    INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.entity_id
    INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
    INNER JOIN `catalog_product_entity_int` AS `cpsd`
      ON cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 96
    LEFT JOIN `catalog_product_entity_int` AS `cpss`
      ON cpss.entity_id = ccp.product_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 1
    INNER JOIN `catalog_product_entity_int` AS `cpvd`
      ON cpvd.entity_id = ccp.product_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 102
    LEFT JOIN `catalog_product_entity_int` AS `cpvs`
      ON cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 1
    INNER JOIN `catalog_category_entity_int` AS `ccad`
      ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 51
    LEFT JOIN `catalog_category_entity_int` AS `ccas`
      ON ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id = 1
  WHERE
    (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (4, 2)) AND
    (IFNULL(ccas.value, ccad.value) = 1) AND (`cc`.`entity_id` >= 501 * 23) AND (`cc`.`entity_id` < 500 * 24);