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

qlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von R Smith >Gesendet: Donnerstag, 02. März 2017 16:10 >An: sqlite-users@mailinglists.sqlite.org >Betreff: Re: [sqlite] Non-unique columns in unique index > > >On 2017/03/02 4:44 PM, Keith Medcalf wrote: >> On Thursday, 2 March

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-uniq

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] 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

On Behalf Of R Smith Sent: Thursday, March 2, 2017 2:50 AM To: sqlite-users@mailinglists.sqlite.org Subject: 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

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

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

017 12:57 PM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Non-unique columns in unique index > > > On 1 Mar 2017, at 8:21pm, David Raymond <david.raym...@tomtom.com> wrote: > > > The trigger version you asked about

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

. 10.0% SQLITE_STAT1.. 10.0% -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, March 01, 2017 4:38 PM To: SQLi

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

nderson Sent: Wednesday, March 1, 2017 2:19 PM To: p...@sandersonforensics.com Cc: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Non-unique columns in unique index Hmm - a bit of google foo and refreshing of chances of collions means my 1:9,223,372,036,854,

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

Hmm - a bit of google foo and refreshing of chances of collions means my 1:9,223,372,036,854,775,808 is way off That is the chance of any two hashes colliding - you'll have lot sof hashes. The basic idea might be sound though even if you stick with a full MD5 it should save a chunk of storage

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

As a bit of an off the wall suggestion you could try an MD5 (or even a partial MD5 - half of the bytes) CREATE table hashes ( hash integer primary key; // just the first 64 bits of the hash of uniquecol and extracol ) as an integer primary key the hash would be an alias of the rowid and so

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

On 1 Mar 2017, at 9:41pm, Deon Brewis wrote: > Yeah ok, but that is paltry compared with the gb's of diskspace that the > actual second index takes up. But thanks for clarifying. Ah. If it’s really GBs of disk space then I can see why you’d look for alternative solutions.

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

:38 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Non-unique columns in unique index On 1 Mar 2017, at 9:11pm, Deon Brewis <de...@outlook.com> wrote: > "But look how much space and processing time it would take up" > >

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

On 1 Mar 2017, at 9:11pm, Deon Brewis wrote: > "But look how much space and processing time it would take up" > > Can you clarify what you mean by "space" ? Your triggers have to be stored. Every time they’re activated (each time you insert a row) they have to be run.

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

mailinglists.sqlite.org> Subject: Re: [sqlite] Non-unique columns in unique index On 1 Mar 2017, at 8:21pm, David Raymond <david.raym...@tomtom.com> wrote: > The trigger version you asked about would look something like the below I > believe. More risky than having the two ind

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

-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in unique index Deon Brewis wrote: > Is there way to add non-unique columns in a unique index? > > I would like to use the same index to enforce unique constraints, as well as > giving a covered result for other queries

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

On 1 Mar 2017, at 5:00pm, Deon Brewis wrote: > Is there way to add non-unique columns in a unique index? I don’t know of anything that operates in that way. > If not, is there a way to efficiently implement a UNIQUE constraint in a > different way? (Trigger maybe?) Not