hyJR2n2K

1The import process is much improved in the latest version but the
2catalog category index process is still a problem if you have a larger
3catalog with multiple stores. Under testing with a catalog over 2
4million SKUs the index process can take over 24 hours. The issue is
5the following statement from within the index process.
6
7SELECT 3 AS `category_id`, `e`.`entity_id` AS `product_id`, 0 AS
8`position`, 1 AS `is_parent`, 1 AS `store_id`, `ei`.`visibility` FROM
9`catalog_product_entity` AS `e`
10  ->  INNER JOIN `catalog_category_product_index_enbl_idx` AS `ei` ON
11ei.product_id = e.entity_id
12  ->  LEFT JOIN `catalog_category_product_index_idx` AS `i` ON
13i.product_id = e.entity_id AND i.category_id = '3' AND i.store_id =
14'1'
15  ->  WHERE (i.product_id IS NULL)
16
17This statement results in the database attempting to create a huge
18temporary table including many millions of records before running the
19final insert.
20
21The reason for this is clarified by asking mysql to explain the query:
22
23mysql> explain SELECT 3 AS `category_id`, `e`.`entity_id` AS
24`product_id`, 0 AS `position`, 1 AS `is_parent`, 1 AS `store_id`,
25`ei`.`visibility` FROM `catalog_product_entity` AS `e`
26  ->  INNER JOIN `catalog_category_product_index_enbl_idx` AS `ei` ON
27ei.product_id = e.entity_id
28  ->  LEFT JOIN `catalog_category_product_index_idx` AS `i` ON
29i.product_id = e.entity_id AND i.category_id = '3' AND i.store_id =
30'1'
31  ->  WHERE (i.product_id IS NULL)\G
32*************************** 1. row ***************************
33         id: 1
34 select_type: SIMPLE
35      table: e
36       type: index
37possible_keys: PRIMARY
38        key: FK_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE
39    key_len: 2
40        ref: NULL
41       rows: 503313
42      Extra: Using index
43*************************** 2. row ***************************
44         id: 1
45 select_type: SIMPLE
46      table: ei
47       type: ref
48possible_keys: IDX_PRODUCT
49        key: IDX_PRODUCT
50    key_len: 4
51        ref: magento.e.entity_id
52       rows: 1
53      Extra:
54*************************** 3. row ***************************
55         id: 1
56 select_type: SIMPLE
57      table: i
58       type: ALL
59possible_keys: NULL
60        key: NULL
61    key_len: NULL
62        ref: NULL
63       rows: 2653541
64      Extra: Using where; Not exists
653 rows in set (0.00 sec)
66
67----------------------------------------------------------------------
68
69In order to improve the performance we simply need to add the
70following index to the table. This will take the catalog category
71indexing down to 7 minutes within our testing.
72
73CREATE INDEX catalog_cat_product_idx ON
74catalog_category_product_index_idx(product_id,category_id,store_id);
75
76This is a massive performance boost if you need to run the entire
77indexing process.