--- Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Joe Wilson <developir-/[EMAIL PROTECTED]> wrote:
> > SELECT col2 FROM t
> > WHERE +col1='foobar' AND col4='foobarfoobarfoobarfoob';
> >
> > With the above where clause,
> > the unique index (col4, col5, col1) will be used instead of
> > the less efficient PRIMARY KEY(col1, col2, col3, col4, col5).
>
> Shouldn't the original query be able to use UNIQUE(col1, col4, col5)
> index?
On my copy of 3.3.5+ it does not.
CREATE TABLE t (
col1 text NOT NULL,
col2 integer NOT NULL,
col3 integer NOT NULL,
col4 text NOT NULL,
col5 text NOT NULL,
PRIMARY KEY(col1, col2, col3, col4, col5)
);
CREATE UNIQUE INDEX t145 on t (col1, col4, col5);
CREATE UNIQUE INDEX t451 on t (col4, col5, col1);
CREATE UNIQUE INDEX t541 on t (col5, col4, col1);
sqlite> explain query plan SELECT col2 FROM t WHERE col1='foobar' AND
col4='foobarfoobarfoobarfoob';
0|0|TABLE t WITH INDEX sqlite_autoindex_t_1
sqlite> explain query plan SELECT col2 FROM t WHERE +col1='foobar' AND
col4='foobarfoobarfoobarfoob';
0|0|TABLE t WITH INDEX t451
The use of index t451 would make the query in question run quite
fast given his data set.
>
> Igor Tandetnik
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
