On 16 Apr 2010, at 1:17pm, Mike Goins wrote:
> 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);
>
> Example insert:
> INSERT INTO tb_file (basename, extension, path) VALUES ('aa', 'bb', 'cc');
>
> Query:
> SELECT tb_file_key, basename, extension, path FROM tb_file WHERE
> basename GLOB 'a*' AND extension GLOB 'b*' AND path GLOB 'c*' AND
> deleted = 0 ORDER BY tb_file_key DESC;
Move the 'deleted' to the beginning of the INDEX. It allows the selector to
reject more records faster (reject 97% and it's faster to match integers).
Also take a look at the other columns in your WHERE clause and rearrange the
other entries in the index to move the ones with the most distinction to the
left.
As you nearly commented in your question you could run something at startup or
shutdown to DELETE aged entries.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
