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)
Also, I don't recall your saying whether a single composite index was faster
than separate indexes? Is it?
Regards
Tim Romano
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);
>
> 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;
>
>
> It's basically something to track existing files on a file system for
> an embedded device.
> As new files are added, new entries are made, and when files are
> removed the deleted column is set to 1 (Only one writer process, 5
> readers). There is not any type of VACUUM since there is not any
> shortage of space and the readers need access nearly all the time.
>
> My query gets slower as the table grows larger. The count of the
> deleted = 0 remains relatively constant while the virtually deleted
> (=1) grows. At 3000 deleted and 75 not, the query runs 4-5 times
> slower then when just the 75 not. The data lookup does not need to
> necessarily fast, while I prefer to minimize the growth in query .
>
> Some solutions I am looking at to minimize
> 1. Delete aged entries.
> 2. Delete aged entries and enable a vacuum mode that does not starve
> readers.
> 3. Create index on deleted, use that to create a TEMP table on which
> the query is run.
> 4. Optimize the query, part we have discussed already to remove the GLOB.
> 5. Fix? the index?
>
> Can I dismiss any of these right of the bat? I'm a little baffled
> with 4 and 5 and may need a couple suggestions.
>
> Thanks again.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
