I skimmed through this whole thread to see if there was any mention of Dijkstra, but didn't see any so here it is: http://www.cs.utexas.edu/users/EWD/transcriptions/EWD08xx/EWD831.html[^] Personally, I prefer 0-based indexes. It makes code dealing with index comparison less clumsy for me.

I don't know your domain or the requirements of the query, but you mentioned Trees, Recursion, and CTEs. Maybe you already know about the Nested Set model? There is a good article about it at http://dev.mysql.com/tech-resources/articles/hierarchical-data.html[^]. This can improve the performance of queries of hierarchal data at a cost during insert and update. It can also massively simplify the SQL you use to query. Sorry if this is all well known... maybe you're already using it! :-O