> Just guessing, but column 'path' probably has greater cardinality than > column 'extension'. What happens if you reverse the order of these columns > in the index? > i.e. (basename, path, extension, deleted)
(deleted, basename, extension, path) This order actually provides the most to least significance for elimination. 'path' is usually the same but does not have to be. > Also, I don't recall your saying whether a single composite index was faster > than separate indexes? Is it? > > Regards > Tim Romano Interesting suggestion. Separate indexes look to be slightly faster than the composite. Separate indexes seem more significant in retrieving data that is at the end of the table. Most of the timing shows <10 millisecond queries either way. Timing separate indexes vs. the following look the same: create index idx_tb_file_deleted on tb_file(deleted); create index fullpath_idx on tb_file (basename, extension, path); Thanks > On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins > <mike.go...@adtecservices.net>wrote: > >> Sorry, this may look a bit familiar. >> >> Table structure: >> CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY >> AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted >> INTEGER default 0 ); >> >> Index: >> CREATE INDEX fullpath_idx on tb_file (basename, extension, path, deleted); >> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
