On 18 May 2014, at 3:19am, Scott Robison <sc...@casaderobison.com> wrote:
> It is easy enough to read the source data and insert it into a table. It is > easy enough to query the list of data ordered by any field after the data > has been completely read. The hard part is knowing the correct insertion > point for a newly read record. Should it go into the current window of the > data? Does it go *above* the current window? Or is it *below*? Just want to check: this is what it sounds like, right ? Your user has a window open where they are scrolling through the table, ordered by SomeColumn. You need to know whether the window needs to change to reflect the new row. Is that right ? > SELECT * FROM SomeTable ORDER BY SomeColumn OFFSET 249999 LIMIT 1 > > SELECT COUNT(*) FROM SomeTable WHERE SomeColumn = value ORDER BY SomeColumn > > As I understand it, both of these have to execute the full query and > calculate the result set in order to do return a result. I need something > that gives me log N access (or better) to elements by key and numeric index. The second SELECT doesn't need the ORDER BY. It will have no effect. How many columns does a table have ? Roughly. If it's not a lot all you need to is create one index for each column that might be chosen as 'SomeColumn'. Or have you already done this and the speed your complaining about is the speed with the index ? One technique I used to use in the old days (long before SQL) was to keep a second table which contained just the rows that were shown on the display. So you'd have your full database on backing store and then another table with just 20 rows. To see whether your new row will effect the display, you just compare it with the first and last entries in the display table, which is very small so it's fast to do things with. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
