-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/15/2010 08:58 PM, Cory Nelson wrote: > This might not be doing what you think it is.
It does exactly what I think it does and as is documented. > When you have a query that will do a full table scan multiple times, > SQLite can sometimes create an index to make it only do a full table > scan once, and use the index every time after that. The index doesn't > stick around though -- it is deleted as soon as your query ends, and > remade every time you run it. Yes, that is exactly what the documentation says. > Since they are created per-query, > there's no way to tune them prior to when you run them. I don't want to tune the automatic indices - I want to see what columns they are on. > While faster than the alternative, it's still very expensive and > really only useful if your queries are very rare and can stand to run > slowly, I'll be the judge of if they are useful to me :-) I've been playing around with my data set imported from somewhere else, and before I have created indices. I was pleasantly surprised by the performance, and this was why. > or if you've got no clue what queries will be run. I don't actually know what queries will be run, yet. The data will be somewhat denormalised and exported to yet another database with a better schema. The current one grew over many years and hence is a mess of spaghetti tables, joins, duplication and inconsistencies. > They're > not meant to make development easier by replacing permanent indexes. The time to create the automatic indices is pretty quick (a second or two) and I will initially develop my code without permanent ones unless performance appears to be an issue. This will make development easier since I won't have to keep adding permanent indices nor repeatedly check that the permanent indices are still appropriate to the queries being developed. I'm all for SQLite making development easier! > It sounds like you already know which columns will be queried against. I won't know until my code is finished, the export has been hand inspected etc. > You should probably be creating permanent indexes. If I knew what columns the automatic indices were on then a first approach is to automate the generation of the permanent ones. Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkyRq+kACgkQmOOfHg372QS7OACeKicD+LdZ59ue03kevg8S3sTa 1CcAoIaJLBIDCflXPU3nBbS8dnGjECk/ =xixA -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
