--- [EMAIL PROTECTED] wrote: > "=?ISO-8859-1?Q?P=E9ter_Szab=F3?=" <[EMAIL PROTECTED]> wrote: > > > > My schema is the following: > > > > 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), > > UNIQUE(col1, col4, col5), > > UNIQUE(col4, col5, col1), > > UNIQUE(col5, col4, col1), > > ); > > > > > > This is also instant, possibly because SQLite is using UNIQUE(col1,col4,col= > > 5): > > > > SELECT col5 FROM t WHERE col1='foobar' AND > > col4='foobarfoobarfoobarfoob'; > > > > However, this is very slow in SQLite 3.3.5: > > > > SELECT col2 FROM t WHERE col1='foobar' AND > > col4='foobarfoobarfoobarfoob'; > > > > The second one will be much faster if you add the "col2" column > to the end of your first UNIQUE constraint: > > UNIQUE(col1, col4, col5, col2),
The initial email says that col1 is always equal to 'foobar', and there are only 96 rows where col4='foobarfoobarfoobarfoob'. Wouldn't UNIQUE(col4, ...who cares...) be more efficient for his specific data set even if the table had to be hit to get the data corresponding to the key? > > Each UNIQUE constraint creates an index. In the case > where you have only the first 3 columns in the index, > the first 2 columns are used to lookup the rowid of > the table, then a binary search is performed for each > row of the table. If there are N rows in the entire > database and K rows of result, then this query requires > time O(KlogN). But if col2 is already part of the > index entry, there is no need to perform that binary > search and the value of col2 is extracted directly from > the index itself. This results in O(K + logN) run time, > which can be much faster, especially if the database > file is not in your disk cache. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
