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

reindex-non-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 13687 rows affected in 3s 942ms X 32 queries ~ 96s
INSERT 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`,
    1                AS `is_parent`,
    1                AS `store_id`,
    IFNULL(
        cpvs.value,
        cpvd.value)  AS `visibility`
  FROM
    `catalog_category_entity` AS `cc`
    INNER JOIN
    `catalog_category_product` AS `ccp`
      ON
        ccp.category_id
        =
        cc.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
  WHERE
    (
      cc.path
      LIKE
      CONCAT(
          '1/2',
          '/%'))
    AND
    (
      cpw.website_id
      =
      '1')
    AND
    (
      IFNULL(
          cpss.value,
          cpsd.value)
      =
      1)
    AND
    (
      IFNULL(
          cpvs.value,
          cpvd.value)
      IN
      (4, 2))
    AND
    (
      `cc`.`entity_id`
      >=
      501)
    AND
    (
      `cc`.`entity_id`
      <
      1001);