Alberto Simões <[EMAIL PROTECTED]>
wrote:
Hi
I am using SQLite to store bigrams, trigrams and tetragrams. Let's
look for one of the tables:
CREATE TABLE trigrams (word1 INTEGER, word2 INTEGER, word3 INTEGER,
occs INTEGER,
PRIMARY KEY (word1,
word2, word3))
My main question is: what's the difference of efficiency between using
this index for the compound key, or three different indexes, one for
each word?
The difference is in what queries the index is going to be helpful with.
If you often run queries like this:
select * from trigrams where word1='a' and word2='b' and word3='c';
then you want a compound index. Three different indexes, one on each
word, won't be particularly useful - only one index will be used to
satisfy one test, the other two tests will be satisfied by a row scan.
If, on the other hand, you run queries like this:
select * from trigrams where word1='a';
select * from trigrams where word2='b';
select * from trigrams where word3='c';
then you want three separate indexes. A compound index could be used
here only when the column tested happens to be the first in the index
(word1 in this example). Otherwise the index won't be used at all.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
