On Sunday, 1 September, 2019 11:12, Alexander Vega <amve...@mail.usf.edu> wrote:
>Thank you Keith for your answer. It has led me to more questions. >"though you may or may not have visited all rows" >From the documentation I did not get the impression that you would >ever not visit ALL ROWS at least once. Is there a technical reason >for this? I would assume a full table scan is walking the un-ordered >leaf pages of the B*tree? How do you know that you are doing a table scan? This certainly cannot be assumed. Perhaps the AuthTable has 57 columns with a total length of several hundred bytes per row but there also happens to be an index on a subset of the columns that includes the two columns that you have asked for. Perhaps you are "table scanning" that covering index instead (because it is cheaper than reading the actual table)? There are ways to insist on a table scan (select ... from table NOT INDEXED ...) for example. However, you left it up to the database engine to choose the most cost effective way to answer your select (which is how SQL works ... it is a declarative language ... you declare what you want and the database figures out the best way to go about giving you what you asked for). As a result of updating the first such row thus received the index has now changed such that the row you are operating on became the last row in the index being scanned. Therefore there is no "next" row. You will have visited only one row, even though there might have been millions of rows in the table. >"Your outer query should probably be "select auth_id, expiration from >AuthTable where expiration <= ? order by +auth_id, +expiration" and >binding current_time as the parameter since there is no point in >retrieving rows that you will not be updating is there? " >You are correct that does make sense. I guess I was trying avoid any >ambiguities of a WHERE clause on the SELECT because I do not >understand its behavior in this circumstance. If you cannot understand the behaviour with a WHERE clause, then what would make you think that one without a WHERE clause would be any more transparent, especially given that all Relational Databases are designed to provide you the results you asked for as efficiently as possible? Perhaps in a few days you will discover that you need to create another index for some other purpose, and that causes SQLite3 to obtain what you said you wanted in an entirely different manner. When you make any change to the database do you re-evaluate the implementation details of every previously written SQL statement to see if it still compatible with the details you depended on? What about it you update the version of SQLite3? You should not be dependent on the peculiarities of the implementation since they might change at any time. >You mentioned two database connections to the same database. Is this >going to work if I am using Threadsafe mode = 0? Yes. Threadsafe mode only affects programs having multiple threads making calls into the sqlite3 library. These are independent variables (that is you can have X threads and Y connections, and X is independent of Y) just because you have 47 connections does not mean that you have more than 1 thread, nor does having 47 threads mean that you have more than 1 connection. Threads are commenced with _beginthread (or equivalent for the OS) calls and connections are commenced with sqlite3_open* calls. The _beginthread operations result in the creation of a thread and the sqlite3_open* calls create a database connection -- they are not related to each other in any way. Also consider that it is entirely possible for a program to have hundreds of threads yet still only be single-threaded as far as sqlite3 is concerned if only one of those threads makes use of the sqlite3 library, and that one thread may use hundreds of database connections either serially or in parallel or in some combination thereof. >Would the second connection be done through an attach? No. The attach statement attaches a database to a connection. You have to have opened the connection first. Connections are created with the sqlite3_open* functions which return a pointer to a database connection. >Does this conversation change if I wrap the whole select and updates >in one transaction? e.g. BEGIN...END No, because isolation is only BETWEEN connections, not WITHIN connections. And the transaction state is per connection. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
