I'm on MySql 8, I am trying to implement Preorder Tree Traversal
I have 65,000 rows which i traverse, the following T-SQL takes minutes to execute in MySql yet, takes milliseconds in MsSql
SELECT tbl_tree.id, (COUNT(depthJoin.id) - 1) AS depth FROM tbl_tree CROSS JOIN tbl_tree AS depthJoin WHERE (tbl_tree.lft BETWEEN depthJoin.lft AND depthJoin.rgt) AND (tbl_tree.lft > 1 AND tbl_tree.lft < 999999999999) GROUP BY tbl_tree.id ORDER BY NULL
I have tried to Optimize innodb buffer pool, but still dont understand what i am missing.
What could i do to improve the Performance in MySql?
Thanks in Advance.