3 ms·

Would you elaborate on how to select data in the custom order efficiently? What you're describing can't be easily indexed, if I understand you correctly. The st

by grzm 9y ago

Would you elaborate on how to select data in the custom order efficiently? What you're describing can't be easily indexed, if I understand you correctly. The strategy outlined in the submission ensures minimal table rewrites on updates (insertion, reordering, deletion) as well as efficient retrieval.

rdiddly 9y ago
Well I think this achieves minimal rewrites, but you do lose a bit on indexing. You can't retrieve by numeric position index for example. But reproducing the order is just a matter of starting from the head (where PrecededById is null) or from any point and then repeatedly selecting the entry whose PrecededById = the current ID.
- grzm 9y ago
  That's more than a bit on indexing. Following a linked list through repeated selections is not nearly as efficient as an index scan.
  - rdiddly 9y ago
    Yeah that's true. Oh well, fractions it is then!
dzamo_norton 9y ago
with recursive todo_list_sorted as ( select * from todo_list tl1 where prev_id is null union all select tl1.* from todo_list tl1 join todo_list_sorted tl2 on tl1.prev_id = tl2.id ) select * from todo_list_sorted
- grzm 9y ago
  Yes, there are ways to do it. The question is how to do so efficiently. Recursive queries don't benefit from indexes as efficiently as, for example, the solution outlined in the submission.