On Mon, Apr 14, 2008 at 10:06:27AM -0700, Chris Tracy scratched on the wall: > > Your first sentence above is that you "almost immediately get back all > > the results you want" then you go on to say that the "actual SELECT > > doesn't return for almost 13 additional seconds." So, what is it > > exactly that you "almost immediately get back"? > > When I run the select inside the sqlite3 command line, I get back > all the results I'm after within a split second. However, the actual > command itself doesn't return for 13 additional seconds, while the entire > table is scanned for other instances where timestamp equals the value I'm > after. (No additional entries exist, so no additional data is ever > returned, but it always does this scan unless I've created an INDEX)
I assume that only works if you are looking for data that was inserted near the "beginning" of the database (e.g. low timestamp values). If you're looking for data that was more recently inserted (e.g at the "end" of the database), it is going to sit there for 13 seconds and then display the values you're looking for... right? So some kind of short-cut solution will help save some time on average, but not for all cases. > So my question is, can I avoid the need to scan the entire table > for additional values where time = <whatever> without having to CREATE > INDEX (and use almost 100% more disk space) if I guarantee that all rows > are inserted with timestamp in ascending order? Technically no, as SQL doesn't consider rows to be ordered within a table. In theory, the same query run two times can return the rows in a different order. On a more practical side, SQLite will generally return rows in the order of their ROWID, assuming no other ordering is in place. Just be sure you understand how ROWIDs are assigned. If you need to do this from the shell, I'm not sure there is anything you can do. If you're doing this from the C API just look for timestamps that are >= your target and terminate the query as soon as you find one larger than the target timestamp (e.g. stop calling step() and call reset() on the statement). If the number of datapoints at a given timestamp is known (because it is fixed, or because you stashed that value in another table), you could also use a LIMIT clause. That has the added bonus of doing the right thing (even if it takes longer) if the rows somehow get out-of-order. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
