3 ms·
SQLite also supports recursion: https://www.sqlite.org/lang_with.html https://www.sqlite.org/lang_with.html I'm not sure if MySQL does now, but about a decade
by st_goliath 7y ago
SQLite also supports recursion: https://www.sqlite.org/lang_with.html https://www.sqlite.org/lang_with.html
I'm not sure if MySQL does now, but about a decade ago when I did my bachelors, in the database course we switched from doing exercises in MySQL to PostgreSQL when recursion came up. Our local university has a database research group headed by a professor who has a fetish for recursion, so we spent a good chunk of time shoehorning graph and tree problems into SQL. As the story goes, thanks to his constant pestering, DB2 supports quadratic recursion.
- Izkata 7y agoDefinitely measure the performance with sqlite though. I recently found one of my recursive queries had really bad performance compared with using a temporary table, because the CTE didn't create an automatic covering index. Had to separate it out into two queries (which changed it from something like .3s to .009s + .001s).
- SQLite 7y agoIf you will kindly send your problem query to the SQLite developers, we will do what we can to improve the query planner to make it run faster.
- Izkata 7y agoPosted (and reran the numbers with some unnecessary transformations removed): https://sqlite.org/forum/forumpost/e4091b433a https://sqlite.org/forum/forumpost/e4091b433a I may have misremembered since I did go a bit wild on this query (done while learning about recursive queries), but pulling the subquery into a CTE doesn't affect the query plan.