last time i looked into this exact problem for mysql this is the best approach i found, and ended up going with. unfortunately the intuitive solutions are not a

last time i looked into this exact problem for mysql this is the best approach i found, and ended up going with. unfortunately the intuitive solutions are not always as good with sql as more convoluted ones. Another common one is e.g. limit pagination vs range offsets, where the former must unfortunately load all rows preceding the page you want, and the latter can just do an index-based lookup. if rows don't often get deleted, you can keep a row for the items ordered number, but on deletion you would have to relabel all rows (or put up with uneven page item counts)