Re: [sqlite] calculated-value indexes are not covering?

Nico, I respectfully disagree, if you look at my first post you can see that the first query does consider that single value index on s covering. Indeed all the indexes here have all the required data to be covering for their queries. As David says, it seems there is a missed optimization

Re: [sqlite] Mailinglist question

The one sad thing about this really wonderful mailing list is that its archives seem to rank really low in Google searches. Whenever I search for something, I've not encountered the archives with answers yet. Google groups has all the features requested here. I wonder if it can't be set up as a

Re: [sqlite] Mailinglist question

Archive of this list, with less than 24 hours lag, here: Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Mailinglist question

Am Wed, 9 Aug 2017 17:02:42 -0500 schrieb Nico Williams: > I... would like to use NNTP, but I'd need someone to provide the > service. I'm connected to this mailing list as if it was a newsgroup using nntp://news.gmane.org:563/gmane.comp.db.sqlite.general. No authentication required. Of course,

Re: [sqlite] Mailinglist question

On 9 August 2017 at 15:02, Nico Williams wrote: > On Wed, Aug 09, 2017 at 03:48:34PM -0600, Keith Medcalf wrote: >> There is a solution since about 1984 ... it is called NNTP (Usenet >> News). Google Groups is basically Usenet News with a (so some people >> thing -- but

Re: [sqlite] Mailinglist question

On Wed, 9 Aug 2017, Nico Williams wrote: Another thing I've done in the past is: download list archives, run a script to fix the From lines so the archive is then a proper mbox format, then use mutt. :) It helps if the archives keep Message-ID headers. Nico, I've not followed this thread

Re: [sqlite] Mailinglist question

On Wed, Aug 09, 2017 at 03:48:34PM -0600, Keith Medcalf wrote: > There is a solution since about 1984 ... it is called NNTP (Usenet > News). Google Groups is basically Usenet News with a (so some people > thing -- but not I -- I detest so-called web-forums) purty front end > to the news reader.

Re: [sqlite] Mailinglist question

There is a solution since about 1984 ... it is called NNTP (Usenet News). Google Groups is basically Usenet News with a (so some people thing -- but not I -- I detest so-called web-forums) purty front end to the news reader. Gateways between mailman, bitnet, usenet, and a bunch of other

Re: [sqlite] Mailinglist question

On 9 Aug 2017, at 8:10pm, Lars Frederiksen wrote: > Is it possible to read the mails directly on a server instead of receiving > the mails constantly? Sure. See Simon.

Re: [sqlite] Tutorials, books, video about SQLite

Thank you for all your advices concerning books about SQLite. I will have a closer look! Lars -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Nico Williams Sendt: 9. august 2017 22:19 Til: SQLite mailing list Emne: Re:

Re: [sqlite] Tutorials, books, video about SQLite

By far the best generic SQL book, IMO, is the O'Reilly "SQL" Pocket Guide. It's very small and yet fairly comprehensive. It covers Oracle, DB2, SQL Server, and MySQL. But it's very general and brief, and everything it has to teach you is generally applicable to PostgreSQL and SQLite3. Nico --

Re: [sqlite] Tutorials, books, video about SQLite

On Wed, 9 Aug 2017, Lars Frederiksen wrote: I would appreciate very much if you clever people out there have some booktitles or links to tutorials (websites, video etc) about SQLite. The O'Reilly book "Using SQLite" has been the most useful book for me thus far. Bob -- Bob Friesenhahn

Re: [sqlite] Tutorials, books, video about SQLite

On 8/9/17, Lars Frederiksen wrote: > > I would appreciate very much if you clever people out there have some > booktitles or links to tutorials (websites, video etc) about SQLite. > Go so the SQLite homepage https://www.sqlite.org/ and find the "Search" option on the far

Re: [sqlite] Tutorials, books, video about SQLite

Safari Books Online. I believe there are a number of SQLite title there. On Wed, Aug 9, 2017 at 2:06 PM, Lars Frederiksen wrote: > Hi > > > > I would appreciate very much if you clever people out there have some > booktitles or links to tutorials (websites, video etc) about

Re: [sqlite] Mailinglist question

On 8/9/17, 2:45 PM, "sqlite-users on behalf of Nico Williams" wrote: > I'm not fond of web fora, but a good integration would be nice, for sure. An > IMAP service would be fantastic for users like me. The need

Re: [sqlite] Mailinglist question

On Wed, Aug 09, 2017 at 12:38:42PM -0700, Jens Alfke wrote: > > On Aug 9, 2017, at 12:31 PM, Nico Williams wrote: > > (It'd be great to have mostly-read-only public IMAP servers serving > > mailing list archives. I say mostly-read-only because one should want > > to keep

Re: [sqlite] Mailinglist question

> On Aug 9, 2017, at 12:31 PM, Nico Williams wrote: > > (It'd be great to have mostly-read-only public IMAP servers serving > mailing list archives. I say mostly-read-only because one should want > to keep track of what one has read, responded to, flagged, and >

Re: [sqlite] Mailinglist question

On Wed, Aug 09, 2017 at 09:10:58PM +0200, Lars Frederiksen wrote: > Is it possible to read the mails directly on a server instead of receiving > the mails constantly? There are several archives online, and if you're subscribed you can use your list subscription password to use the mailman

Re: [sqlite] calculated-value indexes are not covering?

On Wed, Aug 09, 2017 at 06:59:18PM +, Wout Mertens wrote: > but… index s is covering and only includes the field s? I thought a > covering index was one where all the data needed to satisfy the query is in > index? I would say that all the indexes here conform to that definition? No,

[sqlite] Mailinglist question

Hi Is it possible to read the mails directly on a server instead of receiving the mails constantly? Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculated-value indexes are not covering?

There's the issue of whether SQLite takes the value from the index, or recalculates it from the table data. So for a "covering index" you would need to index all the inputs to the function, for example sqlite> create index lc on t (length(s), s); sqlite> explain query plan select distinct

[sqlite] Tutorials, books, video about SQLite

Hi I would appreciate very much if you clever people out there have some booktitles or links to tutorials (websites, video etc) about SQLite. Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] LSM1 extension

On 8/9/17, Jens Alfke wrote: > > One thing I’m unclear on: Will the LSM1 extension in SQLite3 be able to plug > in at a low level (replacing the b-tree engine), so one can use it with all > the existing features like relational tables and SQL queries; or will it > provide only

Re: [sqlite] calculated-value indexes are not covering?

but… index s is covering and only includes the field s? I thought a covering index was one where all the data needed to satisfy the query is in index? I would say that all the indexes here conform to that definition? https://sqlite.org/optoverview.html 8.0 covering index > If, however, all

Re: [sqlite] LSM1 extension

> On Aug 9, 2017, at 8:23 AM, Nico Williams wrote: > > I must say though, SQLite4 is *brilliant*. I do hope that it either > gets completed and replaces SQLite3, or has all its good ideas folded > into SQLite3. The LSM1 work in SQLite3 seems to point towords the >

Re: [sqlite] calculated-value indexes are not covering?

On Wed, Aug 09, 2017 at 06:48:51PM +, Wout Mertens wrote: > sqlite> create table t(j json, s string); > sqlite> create index s on t(s); > sqlite> create index j on t(json_extract(j, '$.foo')); > sqlite> create index l on t(length(s)); In order for any of these indices to be covering indices

[sqlite] calculated-value indexes are not covering?

Back with more indexing questions :) 12991 $ sqlite3 SQLite version 3.19.3 2017-06-08 14:26:16 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(j json, s string); sqlite> create index s on

Re: [sqlite] Using multi-value indexes for subset queries

Sqlite is just really smart :) Doing a `count(*)` on my table with one constraint of a two-valued index does a table scan and completes in 9ms, and when I force use of the index, it's 100ms. I'll stop trying to second-guess the query optimizer now ;) On Wed, Aug 9, 2017 at 5:11 PM Wout Mertens

Re: [sqlite] LSM1 extension

On Wed, Aug 09, 2017 at 08:26:51AM -0500, Charles Leifer wrote: > sqlite4's future is uncertain. It's not, as I understand, meant to replace > sqlite3 any time soon. I think it was more of a place to try out new ideas > for implementations. I must say though, SQLite4 is *brilliant*. I do hope

Re: [sqlite] Using multi-value indexes for subset queries

Indeed, in trying to reproduce on a simple table it does use the index, even with json_extract values. I must be doing something wrong in my app, thanks. On Wed, Aug 9, 2017 at 5:07 PM David Raymond wrote: > There's a guideline for what sort of things SQLite will

Re: [sqlite] Using multi-value indexes for subset queries

There's a guideline for what sort of things SQLite will look for in an index and a query. There are plenty of more advanced ways to make use of indexes I'm sure, but currently those would fall into "future optimization opportunities." http://www.sqlite.org/optoverview.html In your case the

Re: [sqlite] Using multi-value indexes for subset queries

Your experiments are not reproducible unless you provide at least an indication of the schema. Most probably, something you have not yet considered/revealed makes using the index to look up a irrelevant. Maybe a is declared as "integer primary key", making it an alias of the rowid.

Re: [sqlite] Using multi-value indexes for subset queries

On 8/9/2017 10:50 AM, Wout Mertens wrote: in experimenting with indexes I found that if you create an index on (a, b) and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index, great. However, if you write SELECT * FROM data WHERE a = 1, it won't use the index. Are you sure?

[sqlite] Using multi-value indexes for subset queries

Hi all, in experimenting with indexes I found that if you create an index on (a, b) and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index, great. However, if you write SELECT * FROM data WHERE a = 1, it won't use the index. If you write SELECT * FROM data WHERE a = 1 AND B !=

Re: [sqlite] LSM1 extension

sqlite4's future is uncertain. It's not, as I understand, meant to replace sqlite3 any time soon. I think it was more of a place to try out new ideas for implementations. On Wed, Aug 9, 2017 at 3:10 AM, x wrote: > Thanks Charles. Is sqlite4 available yet? > > From:

[sqlite] [FirDAC][Phys][SQLite]ERROR: Cipher: DB is not encrypted

Hi, I have added my SQLite database to DataExplorer in Delphi 10.1. I get this Errormessage [FirDAC][Phys][SQLite]ERROR: Cipher: DB is not encrypted When double click on some of the nodes or if I try to refresh the node of my database. My Encrypt property in the FDConnection Editor

Re: [sqlite] hex and char functions

Thanks Rowan. I’ve picked up bits and pieces from the various replies to get a basic idea of what’s going on. My question should’ve been posted on the c++ builder forum in the first place. Sorry for wasting everyone’s time. Tom From: Rowan Worth Sent: 09 August 2017

Re: [sqlite] LSM1 extension

Thanks Charles. Is sqlite4 available yet? From: Charles Leifer Sent: 08 August 2017 21:21 To: SQLite mailing list Subject: Re: [sqlite] LSM1 extension There's some information that may be of interest on the sqlite4 wiki: *

Re: [sqlite] Optimizing searches across several indexes

This mailing list is so amazing :) Thank you both, everything is clear now! On Wed, Aug 9, 2017 at 9:08 AM Hick Gunter wrote: > An index is only usable for that subset of a queries' equality constraints > that forms a leading subset of the fields handled by the index. > >

Re: [sqlite] Optimizing searches across several indexes

An index is only usable for that subset of a queries' equality constraints that forms a leading subset of the fields handled by the index. E.g.if you are looking at equality constraints for fields a, b and c in one query, then you need an index whose first three fields are a, b and c (in any

Re: [sqlite] Optimizing searches across several indexes

Wout Mertens wrote: > I have a table with a bunch of data (in json). I want to search on several > values, each one is indexed. However, if I search for COND1 AND COND2, the > query plan is simply > > SEARCH TABLE data USING INDEX cond1Index (cond1=?) > > Is this normal? Yes. A query can use

[sqlite] Optimizing searches across several indexes

Hi, I have a table with a bunch of data (in json). I want to search on several values, each one is indexed. However, if I search for COND1 AND COND2, the query plan is simply SEARCH TABLE data USING INDEX cond1Index (cond1=?) Is this normal? I was hoping it could use the indexes somehow for