Si al ejecutar el indexador desde la consola obtenemos algo así:
[~/dev/shell]# php indexer.php reindexall
Product Attributes index was rebuilt successfully in 00:00:00
Product Prices index was rebuilt successfully in 00:00:00
Catalog URL Rewrites index was rebuilt successfully in 00:00:02
Category Products index process unknown error: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mage19`.`mg_catalog_category_product_index`, CONSTRAINT `FK_MG_CAT_CTGR_PRD_IDX_CTGR_ID_MG_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `mg_catalog_category_entity` )' in /dev/lib/Zend/Db/Statement/Pdo.php:228
Podremos arreglarlo con estas consultas sql:
delete from `catalog_category_product` WHERE product_id not in(select entity_id from `catalog_product_entity`);
delete from `catalog_category_product` WHERE category_id not in(select entity_id from catalog_category_entity);
delete from `catalog_product_website` WHERE product_id not in(select entity_id from catalog_product_entity);
delete from `catalog_product_index_eav_idx` WHERE entity_id not in(select entity_id from catalog_product_entity);
delete from `catalog_product_index_eav` WHERE entity_id not in(select entity_id from catalog_product_entity);
delete from `catalog_product_link` WHERE product_id not in(select entity_id from catalog_product_entity);
delete from `catalog_product_relation` WHERE parent_id not in(select entity_id from catalog_product_entity);
delete from `sales_flat_quote_shipping_rate` WHERE `address_id` not in(select `address_id` from `sales_flat_quote_address`);
Como siempre, mucho cuidado con ejecutar consultas SQL contra la base de datos de producción