> Quick question, SQLites, > CREATE TABLE test (col1 text, col2 text, col3 text); > CREATE INDEX tindex ON test (col1, col2, col3); > explain query plan > SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c'; > The above returns: > SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows) > Which of the following is happening: > 1. It's actually only using it as an "index" for the col1 check, and then > just using the fact that, somewhere, it contains the rest of the data for > the query inside the index, which is, in theory, faster than table > scanning the actual table for the results, but you're getting no search > performance gain out of anything other than the col1 part of the index. > 2. It's actually somehow using it as an optimized index over both col1 and > col3 conditions, but only saying col1 in the explain. > 3. Other..?
#1. The index is used to lookup the first row where col1 = 'a'. The index contains all the rest of the data required to satisfy the query, so a scan of the index is all that is used. Values of col1, col2 and col3 are retrieved from the index and returned while col1 = 'a' and where col3 = 'c'. The query is satisfied entirely from the index scan -- there is no need to access the underlying table test at all. > Thanks! > -David --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
