Re: [sqlite] Non-unique columns in unique index

Thank you, that is exactyy what I intended. If sqlite would choose to use the covering index in cases where read speed (plus row decode) is likely to be better, then it would already "sort of" support the INCLUDE syntax requested by the OP with only minor changes. Note that my investigation

Re: [sqlite] SQLite 3 locking

On 1 March 2017 at 02:39, Matthew Ceroni wrote: > > So since busy_timeout defaults to 0, all write attempts if a lock can't be > obtained will return SQLITE_BUSY immediately. Where does the PENDING lock > come into play here? I thought the PENDING was meant to be an

Re: [sqlite] 'start of day' modifier and Julian day timestring

On 3/2/17, Mark Brand wrote: > I am wondering why the 'start of day' and 'start of year' modifiers > don't seem to work on Julian day timestrings. https://www.sqlite.org/src/timeline?y=ci=081dbcfb Tnx for the bug report. -- D. Richard Hipp d...@sqlite.org

[sqlite] 'start of day' modifier and Julian day timestring

Hi, I am wondering why the 'start of day' and 'start of year' modifiers don't seem to work on Julian day timestrings. What is the explanation for the NULLs in examples 2 and 3 below? SELECT example, timestring, strftime('%J', timestring), strftime('%Y-%m-%d %H:%M',

Re: [sqlite] Non-unique columns in unique index

I live my life one "indexed by" at a time. - Deon -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 2, 2017 7:10 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in

Re: [sqlite] Non-unique columns in unique index

On 2017/03/02 4:44 PM, Keith Medcalf wrote: On Thursday, 2 March, 2017 06:04, Hick Gunter wrote: I think what Hick tried to show was that if you have a covering Index on fields F1 and F2 (Unique or not) and then have another index (Automatic or not, but Unique) on just

Re: [sqlite] sqlite-users Digest, Vol 111, Issue 2

Hi Dan, actually I was using the version 3.15.1 so at first I didn't investigate too carefully. However, the TCL script should be using the 3.15.2. To dispel every doubt I updated to the latest 3.17.0 and I am seeing performance way more reasonable, and the bottleneck that I am seeing are

Re: [sqlite] Non-unique columns in unique index

I tried to create a test table and two indices thus: >create temp table test (id integer primary key, name text unique, bs integer, >data text); >create unique index plain on test(name); >create unique index cover on test(name,bs); NB: The field name has a unique constraint As long as the

Re: [sqlite] Non-unique columns in unique index

On 2017/03/02 2:29 PM, Deon Brewis wrote: "This Query (for instance) will be exceedingly fast: SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end" No, that's not covered. I've tried that before, that query is too slow when it isn't covered - the table is many

Re: [sqlite] Non-unique columns in unique index

"Any Index that starts with a Unique column will by definition be Unique." Sorry, yet, I admit the title of the thread is confusing. What I meant to say is that it's TOO unique :). Adding the additional columns will allow duplicates on the columns where duplicates should not be allowed. "This

Re: [sqlite] Non-unique columns in unique index

Any Index that starts with a Unique column will by definition be Unique. Of course in your case you want the Uniqueness of only the first column to be enforced, but you want to lookup also using the second column (either or both). Why insist on having it in a covering Index though? Why not