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, 2017
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
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 F1, and you then do a
On Thursday, 2 March, 2017 06:04, Hick Gunter wrote:
> 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);
als" answer was known by the first column.
- Deon
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of R Smith
Sent: Thursday, March 2, 2017 5:00 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Non-unique columns i
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 query
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 GB
s-boun...@mailinglists.sqlite.org] 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 wan
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 jus
;
> Sure would be nice to have INCLUDE columns support (here and in other
> places).
>
> - Deon
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Wednesday, March 1, 2017 12:57 PM
>
... 30206 15.5%
SQLITE_MASTER. 10.0%
SQLITE_STAT1.. 10.0%
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Simon Slavin
Sent
half Of Paul Sanderson
Sent: Wednesday, March 1, 2017 2:19 PM
To: p...@sandersonforensics.com
Cc: SQLite mailing list
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,775,808 is way off
That is the chance
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
Pa
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
st
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.
But I have a 43 GB
017 1:38 PM
To: SQLite mailing list
Subject: 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 trigg
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. That requires SQLite t
bject: Re: [sqlite] Non-unique columns in unique index
On 1 Mar 2017, at 8:21pm, David Raymond wrote:
> The trigger version you asked about would look something like the below I
> believe. More risky than having the two indexes, but should work. (Famous
> last words)
I have no doubt it w
On 1 Mar 2017, at 8:21pm, David Raymond wrote:
> The trigger version you asked about would look something like the below I
> believe. More risky than having the two indexes, but should work. (Famous
> last words)
I have no doubt it would work. But look how much space and processing time it
ewis
Sent: Wednesday, March 01, 2017 1:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] Non-unique columns in unique index
Thanks, thought about it. But I have a ROWID requirement as well due to foreign
keys referring back to this table via the ROWID.
I could in theory put that just the RowId in
On Wed, 1 Mar 2017 17:00:16 +
Deon Brewis wrote:
> If not, is there a way to efficiently implement a UNIQUE constraint
> in a different way? (Trigger maybe?)
I'll defer to others about how SQLite will respond, but I'd be tempted
to do this:
create table Foo(UniqueCol primary key, Ex
-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
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.
>
> CREATE UNIQUE INDEX indx ON Foo(UniqueCol) INCLUDE (ExtraCol)
CREATE TABLE Foo (
Uniq
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 efficiently. You coul
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.
Something like an 'INCLUDE' would also work (actually even better). E.g.
CREATE UNIQUE INDEX indx ON Foo(UniqueCol)
25 matches
Mail list logo
