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

[Hick Gunter]

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 involved sqlite version 1.7.14.1; maybe the NGQP 
already handles this efficiently.

Also thanks to Keith for pointing out the AUTOMATIC label for ad-hoc generated 
indices.

BTW: My first name is Gunter

>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-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 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 Query of the form:
>
>SELECT F1,F2 FROM T WHERE F1 = x AND F2 = y
>
>SQLite will use the covering Index (as expected), but if you drop one WHERE 
>term so as to end up with the form:
>
>SELECT F1,F2 FROM T WHERE F1 = x
>
>Then SQLite will use the other Index when the covering Index is really better 
>(for read-speed) because it contains all the fields referenced and we "know" 
>that F1 is Unique so the Covering Index must still be Unique >for F1. So an 
>Optimization would be that if we "know" F1 to be Unique, and all the fields 
>required (in the SELECT) are found in the covering Index, then using the 
>covering Index will be better.
>


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3 locking

[Rowan Worth]

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 intermediary
> step before EXCLUSIVE. Does the busy_timeout impact the writers attempt to
> obtain PENDING? Or does the busy_timeout trigger after X amount of ms
> between obtaining PENDING and trying to move to EXCLUSIVE?
>

The writer will be able to grab the PENDING lock immediately, because it
already has the RESERVED lock. The PENDING lock will cause any subsequent
read transactions to fail (with SQLITE_BUSY), but doesn't affect active
read transactions. Thus escalating to EXCLUSIVE can still fail with
SQLITE_BUSY when active readers are present.

Usually this happens when committing a transaction (unless the transaction
exceeds sqlite's memory cache causing it to start writing the DB
mid-transaction). If COMMIT fails with SQLITE_BUSY, the transaction
*remains open*. The application can retry the COMMIT at a later stage (when
hopefully the readers have finished).

So even with a busy_timeout of zero PENDING serves a purpose. And even with
a non-zero busy_timeout COMMIT can fail with SQLITE_BUSY (if there is an
active read transaction which runs for longer than the specified timeout).


It looks like the timer is reset for each call to sqlite3_step.


Note that when waiting for a lock via busy_timeout, the thread is
essentially uninterruptible (sqlite3_interrupt has no effect).

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Mark Brand]

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',  timestring),
strftime('%Y-%m-%d %H:%M',  timestring, '+21 minute'),
strftime('%Y-%m-%d %H:%M',  timestring, 'localtime'),
strftime('%Y-%m-%d %H:%M',  timestring, 'start of day')
FROM (
  SELECT 1 example, 'now' timestring
UNION SELECT 2, strftime('%J', 'now')
UNION SELECT 3, 2457754
UNION SELECT 4, strftime('%Y-%m-%d %H:%M', 2457754)
UNION SELECT 5, strftime('%Y-%m-%d %H:%M', 'now')
) X
ORDER BY example


"1""now""2457815.431712523""2017-03-02 22:21" "2017-03-02 
22:42""2017-03-02 23:21""2017-03-02 00:00"
"2""2457815.431712523""2457815.431712523" "2017-03-02 22:21"
"2017-03-02 22:42""2017-03-02 23:21""NULL"
"3""2457754""2457754""2016-12-31 12:00" "2016-12-31 
12:21""2016-12-31 13:00""NULL"
"4""2016-12-31 12:00""2457754""2016-12-31 12:00"
"2016-12-31 12:21""2016-12-31 13:00" "2016-12-31 00:00"
"5""2017-03-02 22:21""2457815.43125" "2017-03-02 22:21"
"2017-03-02 22:42""2017-03-02 23:21""2017-03-02 00:00"



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Deon Brewis]

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 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 F1, and you then do a Query of the form:

SELECT F1,F2 FROM T WHERE F1 = x AND F2 = y

SQLite will use the covering Index (as expected), but if you drop one WHERE 
term so as to end up with the form:

SELECT F1,F2 FROM T WHERE F1 = x

Then SQLite will use the other Index when the covering Index is really better 
(for read-speed) because it contains all the fields referenced and we "know" 
that F1 is Unique so the Covering Index must still be Unique for F1. So an 
Optimization would be that if we "know" F1 to be Unique, and all the fields 
required (in the SELECT) are found in the covering Index, then using the 
covering Index will be better.

While I follow the suggestion, I'd like to point out that a covering Index 
might not be the best to use. More than one column could be Unique or the 
covering Index may contain a lot more fields than is referenced, it may not 
always be faster. Imagine these tables:

CREATE TABLE T (ID INTEGER PRIMARY KEY, F1, F2, F3, F4, F5, F6, F7, F8, F9); 
CREATE UNIQUE INDEX TU_1 ON T(F1, F2); CREATE INDEX TC_2 ON T(F1, F2, F3, F4, 
F5, F6, F7, F8, F9);

The query:
SELECT F1, F2 FROM T WHERE F1 = x AND F2 = y; will surely be much faster when 
using the implied Unique index, and further:

SELECT F3 FROM T WHERE F1 = x AND F2 = y; should still be faster using the 
(much smaller) Unique Index and reading
F3 after a lookup. If you are in doubt, imagine the same example with Fields 
going up to F999. At some field-count the Unique Index will be faster for any 
selection of field types. It's hard to imagine a safe tweak for the QP here, 
and having to "assess" whether all referenced fields are ALSO in Unique 
indices... sounds like an expensive step, but now I'm just guessing.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 Query of the form:


SELECT F1,F2 FROM T WHERE F1 = x AND F2 = y

SQLite will use the covering Index (as expected), but if you drop one 
WHERE term so as to end up with the form:


SELECT F1,F2 FROM T WHERE F1 = x

Then SQLite will use the other Index when the covering Index is really 
better (for read-speed) because it contains all the fields referenced 
and we "know" that F1 is Unique so the Covering Index must still be 
Unique for F1. So an Optimization would be that if we "know" F1 to be 
Unique, and all the fields required (in the SELECT) are found in the 
covering Index, then using the covering Index will be better.


While I follow the suggestion, I'd like to point out that a covering 
Index might not be the best to use. More than one column could be Unique 
or the covering Index may contain a lot more fields than is referenced, 
it may not always be faster. Imagine these tables:


CREATE TABLE T (ID INTEGER PRIMARY KEY, F1, F2, F3, F4, F5, F6, F7, F8, F9);
CREATE UNIQUE INDEX TU_1 ON T(F1, F2);
CREATE INDEX TC_2 ON T(F1, F2, F3, F4, F5, F6, F7, F8, F9);

The query:
SELECT F1, F2 FROM T WHERE F1 = x AND F2 = y;
will surely be much faster when using the implied Unique index, and further:

SELECT F3 FROM T WHERE F1 = x AND F2 = y;
should still be faster using the (much smaller) Unique Index and reading 
F3 after a lookup. If you are in doubt, imagine the same example with 
Fields going up to F999. At some field-count the Unique Index will be 
faster for any selection of field types. It's hard to imagine a safe 
tweak for the QP here, and having to "assess" whether all referenced 
fields are ALSO in Unique indices... sounds like an expensive step, but 
now I'm just guessing.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simone Mosciatti]

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 
from redis and not SQLite now.


It is worth to investigate further, I will keep the mail list updated 
and I will update the github issue.


Thanks for now.

Best

Simone

On 02/03/2017 13:00, sqlite-users-requ...@mailinglists.sqlite.org wrote:
Message: 10 Date: Wed, 1 Mar 2017 23:08:06 +0700 From: Dan Kennedy 
 To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Performance difference in running SQLite embed 
or in a TCL script Message-ID: 
 Content-Type: 
text/plain; charset=utf-8; format=flowed On 03/01/2017 09:53 PM, 
Simone Mosciatti wrote:

Hi all,


tl;dr:

What are the difference between running SQLite inside TCL and running
it embed in a shared object module? Why I am seeing such big
difference in performance?

https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-283309641


I finally got some time to work again on my redis module rediSQL,
which basically embed SQLite inside redis.

I already asked in this same mail list help about performance, and it
was showed to me that the poor performance of the modules are not
because of SQLite and that I should look at something else.

I have a couple of benchmark where I insert triple of integers inside
a single table, using straight TCL my machine reach a stable 240k
insert per second.

Using the redis module and doing the same kind of operation I got way
worse performance that degrade with time. The insert per second start
at roughly 24k and then decrease down to 1k.

What I did to investigate the different behaviour was to use perf on
both the script TCL and the redis server running the module.

Since the SQLite performances are order of magnitude better than the
redis module performances I was expecting that something redis related
was throttling down the insert per second. I was wrong.

The perf of the TCL script show that most of the time is spent in
libpthread, libc, libtcl and only after in libsqlite in the symbol
sqlite3_randomness.

The perf ot the redis module, instead show that most of the time is
spent in |sqlite3PcacheTruncate | (52%) and then on
|sqlite3PcacheClearWritable |(30%) I must be doing something totally
wrong, but what?

I forget exactly when it was introduced, but there was a bug causing
those routines to use excessive CPU on systems with really big page
caches. Fixed for 3.14.1. Are you using something earlier than 3.14.1?
If so, it's worth trying a newer version.

http://sqlite.org/releaselog/3_14_1.html

Dan.



You can find more details on the issues I opened here:
https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-283309641

To sum up I am asking what are the difference in running redis from
TCL and from running it embed in a shared object.

Thanks for your time.

Simone

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

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 mentions the additional field, sqlite chooses the covering 
index.

>explain query plan select name,bs from test where name='test' and bs=1;
>sele  order  from  deta
>  -    
>0 0  0 SEARCH TABLE test USING COVERING INDEX cover 
>(name=? AND bs=?) (~1 rows)

unfortunately if thsi field is dropped from the where clause but remains in the 
select list, sqlite misses the covering index

>explain query plan select name,bs from test where name='test';
>sele  order  from  deta
>  -    
>0 0  0 SEARCH TABLE test USING INDEX plain (name=?) (~1 
>rows)

not having an index at alls causes sqlite to create an automatic index

>drop index cover;
>drop index plain;
>explain query plan select name,bs from test where name='test';
>sele  order  from  deta
>  -    
>0 0  0 SEARCH TABLE test USING INDEX 
>sqlite_autoindex_test_1 (name=?) (~1 rows)

just the same if the index is not unique (even though the first field is)

>create index plain on test(name);
>create index cover on test(name,bs);
>explain query plan select name,bs from test where name='test';
>sele  order  from  deta
>  -    
>0 0  0 SEARCH TABLE test USING INDEX 
>sqlite_autoindex_test_1 (name=?) (~1 rows)

I guess such a schema would be quite unusual, so this optimization opportunity 
is missed

-Ursprüngliche Nachricht-
>"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.

I don't think so. There can only be one entry for the unique field, and the 
extra column captures the corresponding value of the one and only matching 
record.



___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 and the result is needed in low 
milliseconds. Also, I don't need an index directly on ExtraCol like specified 
below for the ( WHERE ExtraCol = ...) case . It's not useful a useful lookup by 
itself - only a useful result. So the only reason to include it in an index is 
to make it covered.


Ah yes, so you /do/ need every bit of work cycle saving you can get


"You cannot optimize for everything, pick your favourite thing and optimize for 
that."

This is a bit of a call to support INCLUDE columns in indexes. This 
optimization is very easy in SQL Server using that.


Well yes, so in SQLite you can optimize either for the speed or for the 
space, not for both. In MSSQL you can optimize for both using INCLUDE, 
and I can see how it is useful in specifically your case.


That said, I doubt it would make it into development code for SQLite at 
this point for the simple reason that it adds weight to the engine 
(adding INCLUDE) for a very fringe-case optimization.

Perhaps there are other reasons to add INCLUDE that are less fringe-case?


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Deon Brewis]

"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 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 and the result is needed in low 
milliseconds. Also, I don't need an index directly on ExtraCol like specified 
below for the ( WHERE ExtraCol = ...) case . It's not useful a useful lookup by 
itself - only a useful result. So the only reason to include it in an index is 
to make it covered.


"You cannot optimize for everything, pick your favourite thing and optimize for 
that."

This is a bit of a call to support INCLUDE columns in indexes. This 
optimization is very easy in SQL Server using that.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-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 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 just make one Unique index, and one other Index on the other column by 
itself? Like so:

+CREATE TABLE Blah(
 Id Integer Primary Key,
 UniqueCol blob, // 20 bytes fixed
 ExtraCol blob, // 12 bytes fixed
 UNIQUE(UniqueCol)
);  // ~36 bytes/row

CREATE INDEX Blah_ExtraIdx on Blah(ExtraCol);


This will be close to the same size of one covering Index.
The Query planner is clever enough to figure out it can use only the second 
Index to look up some queries, it can also use the Unique Index if needed for a 
query and it will use it to enforce uniqueness.

You get all the speed and a little extra overhead space, but much less than a 
covering Index.

This Query (for instance) will be exceedingly fast:
SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end

So would this:
SELECT * FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end AND 
ExtraCol = xxx;

And this:
SELECT * FROM Foo WHERE ExtraCol = xxx;

All of these will be super fast and your Indices will take up the least 
possible space.

There is some saving in cycles if you can read the bytes directly out of a 
covering Index rather than a big table, but this is no big table, it should be 
real quick. If you really really really need those few cycles saved, invest the 
MBs and make the covering Index additional. If space is a problem, use only the 
single Unique index.

You cannot optimize for everything, pick your favourite thing and optimize for 
that.

Cheers,
Ryan

On 2017/03/01 7:00 PM, 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.
>
> Something like an 'INCLUDE' would also work (actually even better). E.g.
>
> CREATE UNIQUE INDEX indx  ON Foo(UniqueCol) INCLUDE (ExtraCol)
>
>
> If not, is there a way to efficiently implement a UNIQUE constraint in 
> a different way? (Trigger maybe?)
>
> -Deon
>   
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 just make one Unique index, and one other Index on the other 
column by itself? Like so:


+CREATE TABLE Blah(
Id Integer Primary Key,
UniqueCol blob, // 20 bytes fixed
ExtraCol blob, // 12 bytes fixed
UNIQUE(UniqueCol)
);  // ~36 bytes/row

CREATE INDEX Blah_ExtraIdx on Blah(ExtraCol);


This will be close to the same size of one covering Index.
The Query planner is clever enough to figure out it can use only the second 
Index to look up some queries, it can also use the Unique Index if needed for a 
query and it will use it to enforce uniqueness.

You get all the speed and a little extra overhead space, but much less than a 
covering Index.

This Query (for instance) will be exceedingly fast:
SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end

So would this:
SELECT * FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end AND 
ExtraCol = xxx;

And this:
SELECT * FROM Foo WHERE ExtraCol = xxx;

All of these will be super fast and your Indices will take up the least 
possible space.

There is some saving in cycles if you can read the bytes directly out of a 
covering Index rather than a big table, but this is no big table, it should be 
real quick. If you really really really need those few cycles saved, invest the 
MBs and make the covering Index additional. If space is a problem, use only the 
single Unique index.

You cannot optimize for everything, pick your favourite thing and optimize for 
that.

Cheers,
Ryan

On 2017/03/01 7:00 PM, 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.

Something like an 'INCLUDE' would also work (actually even better). E.g.

CREATE UNIQUE INDEX indx  ON Foo(UniqueCol) INCLUDE (ExtraCol)


If not, is there a way to efficiently implement a UNIQUE constraint in a 
different way? (Trigger maybe?)

-Deon
  
___

sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users