> 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?
Chris
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
