I'm on MySql 8, I am trying to implement Preorder Tree Traversal

(Referal : https://gist.github.com/tmilos/f2f999b5839e2d42d751)

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

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.

  • 1
    Please check what tags like sql-server mean before applying them to your question. – Aaron Bertrand Apr 20 at 13:32
  • Apologies Sire, I was actually expecting Experts from both sql-server and mysql. As I Already stated, the query executes with in a second in sql-server, but takes minutes in mysql. If the experts at mutual field were to talk regarding this, i was hoping to get a clear understanding about this strange case. @AaronBertrand – Prabakaran Apr 22 at 8:45
  • This isn’t an open discussion forum and I don’t think you’ll find many SQL Server experts who know MySQL internals, so casting a wide net won’t really help you (but if you're going to cast a wide net, why not also include Oracle, DB2, and PostgreSQL experts too?). As for the performance, show your table structure and any indexes you currently have. "Why is this query slow?" with no other information is going to be pretty hard to answer for people with any expert level in any particular technology. – Aaron Bertrand Apr 22 at 13:57
  • The left-right tree is not recognized and optimized by MySQL. Perhaps it is in SQL Server. Since you are using MySQL 8, I recommend creating an ordinary hierarchical tree and use a CTE to achieve whatever purpose you are doing. CTEs are the future of this type of application, not left-right. – Rick James Apr 28 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.