Sorry, I typoed the second query. Should have been <=. On May 17, 2014 8:40 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:
> > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
