Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

Thanks to all who have replied, very informative! :) This is just a database for own personal use so it's not a big deal in any way, mainly trying to get a better understanding of how Sqlite works here. I'll note that the sql queries are not static inside my application but they are generated

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

I agree that correlated subqueries in general seem more natural and are probably also less likely to have the performance pessimizations noticed with joins. But I might also want to use the column, or in case of a correlated subquery, the column alias, in the WHERE clause and previously that has

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

The data used for sqlite_stat1 in create.txt is taken from the real data, it's copied from the sql-dump generated just after running ANALYZE. I only wanted to include the minimum amount of data the demonstrate the issue so I omitted all other tables, views and data. As I understand it sqlite only

[sqlite] Query planner: Scanning subqueries vs using automatic covering index

I recently noticed some very slow queries for my sqlite book database. Turns out that it happened due to the query planner decided to scan sub-queries instead of using an automatic covering index to search them. The database contains about 3000 entries and with two subqueries it took a

Re: [sqlite] Maximum result set size

A clarification about sqlite3_exec please. Surely that works the same way as sqlite3_step in respect to memory use since it's using step internally and just forwards the results to the callback function of exec. Or did I miss something? -- Sent from: http://sqlite.1065341.n5.nabble.com/

Re: [sqlite] Troubles with sqlile sql

I'm going to reply to both messages at once. On Sat, 28 Jan 2017 15:44:07 -0500 Richard Hipp <d...@sqlite.org> wrote: > On 1/28/17, David Niklas <do...@mail.com> wrote: > > > > # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL > > Error: near "CONSTRAIN

[sqlite] Troubles with sqlile sql

Good evening gentlemen, I read the post: http://linuxgizmos.com/ringing-in-2017-with-90-hacker-friendly-single-board-computers/ which lists a number of SBC. I was interested, but the info in the table was lacking. The article also left out much of what I considered "Interesting" information. It

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - outp ut in column mode does not align UTF8-strings correctl y

Igor Tandetnik <itandet...@...> writes: > > Niklas Bäckman <nikba...@...> wrote: > > Columns with special characters like ("å" "ä" "å") get too short widths when > > output. > > > > I guess this is due to the shel

[sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

widths when output. I guess this is due to the shell not counting actual UTF8 *characters/code points* when calculating the widths, but instead only counting the plain bytes in the strings, so they will seem longer until they are actually printed to the console. The output was done to a Windows console