Hmmm...do I hear a new pragma that would either remember such indexes, or be verbose about creating them? Could just be from the shell I suppose for verbosity. Would really be nice to be able to turn that on for testing. Or does the explain tell you when it will create one? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems
________________________________ From: [email protected] on behalf of Simon Slavin Sent: Wed 1/5/2011 4:32 PM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Query slow on SQLite On 5 Jan 2011, at 5:42pm, Peter wrote: > Both the last two 'tables' are views of the form I gave above. I'm using > the same indexes for both SQLite and PostgreSQL. PostgreSQL will make up its own indexes if it thinks they will speed up a search. Actually, so will SQLite. The problem is that SQLite throws its index away after the SELECT is finished, whereas PostgreSQL does not. Consequently if you don't have good indexes for a SELECT PostgreSQL will make up the index once and cache it for future SELECTs, whereas SQLite will make up the index again every time you execute another SELECT. So take a good look at both your SELECTS and figure out whether you have indexes ideal for every part of the SELECT: the main part and every JOIN and sub-SELECT. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
