I worked with the “explain” command on a postgresql database to optimize some requests
and found it very useful.
now I have to do some kind of same work on a sqlite database and the output of the explain command is completely cryptic !
if you look at the sqlite manual online, it says that as this command is changing often, there is no explanation about it’s output
and you must try by yourself … bah !
is there somewhere (or someone) that can explain me the output of the “explain” under sqlite ?
no - its horrible
probably the thing I hate the most in sqlite
not like anything from any other DB i’ve ever touched that say “oh I’m going to scan this table using this index first , then this one using this index etc etc etc”
its all the micro ops for the internal virtual machine that runs the various steps
This appears yesterday, check the line with EXPLAIN):
[code]SQLite Release 3.14.2 On 2016-09-12
Improved support for using the STDCALL calling convention in winsqlite3.dll.
Fix the sqlite3_trace_v2() interface so that it is disabled if either the callback or the mask arguments are zero, in accordance with the documentation.
Fix commenting errors and improve the comments generated on EXPLAIN listings when the -DSQLITE_ENABLE_EXPLAIN_COMMENTS compile-time option is used.
Fix the ".read" command in the command-line shell so that it understands that its input is not interactive.
Correct affinity computations for a SELECT on the RHS of an IN operator. Fix for ticket 199df4168c.
The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator loop is actually used by the query plan. Fix for ticket 0c4df46116e90f92.
Fix an internal code generator problem that was causing some DELETE operations to no-op. Ticket ef360601
Hashes:
SQLITE_SOURCE_ID: "2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6"
SHA1 for sqlite3.c: bcc4a1989db45e7f223191f2d0f66c1c28946383
Changes carried forward from version 3.14.1 (2016-08-11):
A performance enhancement to the page-cache "truncate" operation reduces COMMIT time by dozens of milliseconds on systems with a large page cache.
Fix to the --rbu option of sqldiff.[/code]
In regards to what Emile posted - that won’t help most Xojo users, though, because (a) we’d have to wait for Xojo to use this new sqlite build, which can take a long time to happen, and (b) it would have to be built with that special option, which is also not automatically the case for Xojo builds. You’d have to make this a feedback request and then blindly hope it’ll be implemented this way one day - of course, without ever getting any hint as to when or even if it’ll happen, because it’s Xojo’s policy not to do that
So, you’d probably have to build your own sqlite, and then use declares to use it. Or maybe ask MBS - that’s likely to happen much quicker, but it’ll cost you a little (for the general MBS license). This may be the most reliable and faster way if you really need this.
