Some products are missing in table catalog_product_index_price!

Question

Anyone can help me clarify the Price Indexing in Magento? I'm using version 1.9.
My task: Rendering the featured products into home page.
My Solution: Instead of creating the category called "Featured product". I created an attribute "is_featured", so I just filter the product with that attribute is true to get the expected result.
Base on the built in widget Mage_Catalog_Block_Product_Widget_New, my function to get the product collection as defined:

protected function _getProductCollection()
    {
        /** @var $collection Mage_Catalog_Model_Resource_Product_Collection */
        $collection = Mage::getResourceModel('catalog/product_collection');
        $collection->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());
        $collection = $this->_addProductAttributesAndPrices($collection)
            ->addStoreFilter()
            ->addAttributeToFilter('is_featured', array('eq' => true))
            ->setPageSize($this->getProductsCount())
            ->setCurPage(1);
        return $collection;
    }

Result: Some products are appeared, but some others is missing. When SQL debugging, I see:

SELECT 
    `e`.*,
    `cat_index`.`position` AS `cat_index_position`,
    `price_index`.`price`,
    `price_index`.`tax_class_id`,
    `price_index`.`final_price`,
    IF(price_index.tier_price IS NOT NULL,
        LEAST(price_index.min_price,
                price_index.tier_price),
        price_index.min_price) AS `minimal_price`,
    `price_index`.`min_price`,
    `price_index`.`max_price`,
    `price_index`.`tier_price`,
    `at_is_featured`.`value` AS `is_featured`
FROM
    `catalog_product_entity` AS `e`
        INNER JOIN
    `catalog_category_product_index` AS `cat_index` ON cat_index.product_id = e.entity_id
        AND cat_index.store_id = '1'
        AND cat_index.visibility IN (2 , 4)
        AND cat_index.category_id = '2'
        INNER JOIN
    `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
        AND price_index.website_id = '1'
        AND price_index.customer_group_id = 0
        INNER JOIN
    `catalog_product_entity_int` AS `at_is_featured` ON (`at_is_featured`.`entity_id` = `e`.`entity_id`)
        AND (`at_is_featured`.`attribute_id` = '210')
        AND (`at_is_featured`.`store_id` = 0)
WHERE
    (at_is_featured.value = '1')
LIMIT 6

The problem is here, catalog_category_product_index, some product disappear is missing in this table? But I don't know, why the index of some products are being miss? I have tried Re-Index so many times without the expected results! Any one can help me? Thanks alot!

Answer 1

If a product is missing from the price index, it's usually because it is not associated to the current website.

To find out how the products are indexed, run the price indexer and debug the query in Mage_Catalog_Model_Resource_Product_Indexer_Price_Default::_prepareFinalPriceData() around line 285 (after the prepare_catalog_product_index_select event has been triggered)

This is for simple products. For other product types, it's the same in their corresponding indexer implementation (subclass of Mage_Catalog_Model_Resource_Product_Indexer_Abstract).