insert into temp category table is kind of slow.

[zhhank]

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,'%')
);
");
}