Hi Richard, Thank you for your answer, much appreciated. I guessed that insertion and deletion could be an issue. We need to re-number keys (change a lot of Btree nodes) on each operation. Or at least on REINDEX command (I say not strictly, just as idea).
What could you advise me? Probably temporary table on each order by? Or just avoid pagination? It's looks the best solution :) as my whole data loads just for 4s. If it's trade off question between 'viewing' and 'editing' (SELECT vs INSERT,DELETE) my app has 'viewing' priority. I'm writing librarian application (Books collection: Author, Title, Genre, Filename, etc). And most time user will search the catalogue (filtering, sorting, scrolling up and down). So I want excellent db viewer, and slow insert could be acceptable. Or batch insert then reindex. How is it difficult to change Btree design and introduce rownum field in the key structure? It could be a great compile-time option! Or should I choose another engine? Do you know about rownum for ms sql ce or firebird? We've used these engines in work project for logging, but ended up with SQLite ;) MS SQL ce is not very portable as it crashs on some systems because some dlls. And firebird feels slow. So SQLite wins! Thanks, Max 2015-01-08 3:23 GMT+03:00 Richard Hipp <d...@sqlite.org>: > On 1/7/15, Max Vasilyev <maxrea...@gmail.com> wrote: > > Hi guys, > > Is it possible to get key number from the index? > > For example let's consider Figure 4: An Index On The Fruit Column from > here > > https://www.sqlite.org/queryplanner.html > > > > index_rownum, fruit, rowid > > 1 Apple 2 > > 2 Grape 5 > > 3 Lemon 18 > > 4 Orange 1 > > 5 Orange 23 > > ... > > > > Is it possible to add rownum column and store it in the index? And get it > > in the query? > > > > No, that information is not available in the SQLite Btree design. > > When I was designing the Btree (back in 2003) I considered adding the > capability to compute the "rownum" in O(logN) time. That would have > made things like "count(*)" much faster too. But doing this also > increases insertion and deletion cost, so I decided against doing it. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
