On 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote: > > 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) > > To wit: > > sqlite3 test.db > sqlite> SELECT * from bridge_table WHERE timestamp = 1167615600; > [within a split second I get several hundred values ending with...] > 1167615600|494|8|2 > 1167615600|495|9|7 > 1167615600|496|5|21 > 1167615600|497|8|24 > 1167615600|498|6|46 > 1167615600|499|1|14 > 1167615600|500|9|31 > [and here it sits for 13 more seconds, looking through the rest of the > table for any other values where time = 1167615600, but none exist, so > finally...] > sqlite> [I get the prompt back once it finishes, having received no > additional output, because there isn't any] > > It seems to me (and I might be wrong) that since the values are > already in the database in ascending order by timestamp that SQLite is > able to find all the results quite quickly. However, it must then scan > through the rest of the database on the off chance that some other rows > exist where timestamp = 1167615600, because it doesn't know that they're > all stored in ascending order. > > 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? >
a shot in the dark. Try SELECT * from bridge_table WHERE timestamp < 1167615601; although, it would probably not help because SQLite would still have to scan the table to ascertain there were no more rows that met the criterion. My hunch is that without giving the program some hint (aka, INDEX) you can't get what you want. But, others on this list likely have much better knowledge of the internals. Good luck. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
