insert into temp category is kind of slow if you have thousands categories existed. And in my case, I need run import data from time to time every day, and the updated data is not very big(<300 products). So I need the import is as fast as possible.
For a quick fix, I create a index on temp table's path column. in my test case, the sql execute time is changed from 5seconds to <1 seconds. Hope this can help someone in the similar case..
Maybe someone have better idea to calculate the children_count.
public function updateChildrenCount() {
.....
$categoryTableTmp = $categoryTable . '_tmp';
$connection->query('DROP TEMPORARY TABLE IF EXISTS ' . $categoryTableTmp);
$connection->query("CREATE TEMPORARY TABLE {$categoryTableTmp} LIKE {$categoryTable};
CREATE FULLTEXT INDEX idx ON {$categoryTableTmp}(PATH);
INSERT INTO {$categoryTableTmp} SELECT * FROM {$categoryTable};
UPDATE {$categoryTable} cce
SET children_count =
(
SELECT count(cce2.entity_id) - 1 as children_county
FROM {$categoryTableTmp} cce2
WHERE PATH LIKE CONCAT(cce.path,'%')
);
");
}
insert into temp category is kind of slow if you have thousands categories existed. And in my case, I need run import data from time to time every day, and the updated data is not very big(<300 products). So I need the import is as fast as possible. For a quick fix, I create a index on temp table's path column. in my test case, the sql execute time is changed from 5seconds to <1 seconds. Hope this can help someone in the similar case..
..... $categoryTableTmp = $categoryTable . '_tmp'; $connection->query('DROP TEMPORARY TABLE IF EXISTS ' . $categoryTableTmp); $connection->query("CREATE TEMPORARY TABLE {$categoryTableTmp} LIKE {$categoryTable}; CREATE FULLTEXT INDEX idx ON {$categoryTableTmp}(PATH); INSERT INTO {$categoryTableTmp} SELECT * FROM {$categoryTable}; UPDATE {$categoryTable} cce SET children_count = ( SELECT count(cce2.entity_id) - 1 as children_county FROM {$categoryTableTmp} cce2 WHERE PATH LIKE CONCAT(cce.path,'%') ); "); }