Category Products Indexing

Question

If I re-index catalog_category_product, shouldn't the tables catalog_category_product and catalog_category_product_index have the same amount of entries afterwards?

If not, what is the difference or what explains it?

If yes, how come they are still different?

Thanks in advance!

Answer 1

They shouldn't have the same number of records.
The table catalog_category_product contains the links you create in the backend. Here is an example. Let's say you have the following category structure. Using category ids:

2
|-4
|-5
  |-6

And in the backed you associate the product with id 99 to the category with id 6.
Then in catalog_category_product you will have:

category_id = 6
product_id = 99
position = 0 (or whatever)

Let's say category 5 is anchor and category 2 is root. After reindexing you will see in catalog_category_product_index you will have 3 records for each store view where the product is available:

category_id|product_id|position|is_parent|store_id|visibility  
2          |99        |P1      |1        |1       |4  
5          |99        |P2      |1        |1       |4   
6          |99        |P3      |0        |1       |4

P1, P2, P3 are the product positions.See how they are calculated in here.
is_parent is 0 if the product is directly related to the category and 1 if it's related to a child category (I'm not 100% sure about this).

Also, from the _index table only enabled products appear.