On 14 Jun 2016, at 9:54pm, Smith, Randall <rsm...@qti.qualcomm.com> wrote:
> Thanks for the ideas, Simon. Already good on the general principles. The > approach of just periodically deleting all the indices and starting over from > scratch with a massive, comprehensive re-profiling effort might work on a > small project, an overstaffed one, one that doesn't change much, or one that > is not in a high-pressure environment, but is definitely not practical on > mine where none of these conditions apply. In a situation like that you do not work on the production database on production hardware, but a test copy on test hardware. But the principles are unchanged. As I wrote, changing the data shouldn't change the indexes needed. That happens only if you stop executing some SQL commands or start executing some new ones. If you have a huge complicated schema with many different SQL commands executed perhaps it would be acceptable to do the process just for each new command: 1) For each new SQLite command, does it execute in acceptable time ? 2) If not, figure out an index which is suited to the new command. Compare this new index and see if it's sufficiently different from existing ones to be worth creating just as it is. 3) If not, replace an existing index with one which incorporates elements of both. An experienced SQL programmer would know a few indexes to create before even running their first test. But doing the process I outlined will teach you how indexes work and let you gain enough experience to do that. > o Some kind of "gee, I sure wish I had this index" info from the query > planner. [and other suggestions which follow from those] Given the above sequence, why not go the whole way and have the software automatically create the new indexes itself ? Or have the SQL engine just make every temporary index it uses while running permanent ? Then you wouldn't need the human at all. Using the automated system outlined above just leads to databases with far too many indexes. Consulting tables takes little time but making changes takes far more because for every row created/deleted many indexes need to be updated. Another problem with the above procedure is that that new indexes made can make old indexes pointless. You need a human to realise when that happens and weed out the old ones. Or to start from a situation where you have no indexes at all. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
