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
