> Another trick you can pull is to create an index that > contains every column in the table with the cluster index > columns occuring first. That will double the size of your > database. But when SQLite can get all of the information it > needs out of the index it does not bother to consult the > table itself. So the index will always stay clustered.
Interesting idea. Will check that out I did the clustering trick (insert into wibble select * from wibble2 order by key1, key2;) which basically sorted the table in the order the query wants it, and there was a noticable speed increase Sure, the table will slowly, as data is added, become less and less "clustered" but frankly it's something I am willing to live with considering the user would have to use the program for 10+ years before the data they added would compare with the data that's already there Now the slowest points of the operation aren't DB access and retrieval any more, but application+parseing of the data once gathered Way to go SQLite :)
