"=?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), 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]>
