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