One thing I would add is to try to populate your example database with representative data - in fact, try hard to figure out what representative data looks like, it informs many decisions. My experience is that sometimes people assume that because something is fast enough on their workstation, it's fast enough for production, but in production their joins involving multiple full table scans are suddenly generating tens of thousands of rows for their SORT to order and their WHERE to filter, instead of the small constant number of rows in their simple test database.
-scott On Mon, Jun 13, 2016 at 5:04 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 14 Jun 2016, at 12:27am, Smith, Randall <rsm...@qti.qualcomm.com> wrote: > >> the info from EXPLAIN QUERY PLAN and from reading the query itself don't >> always lead to an obvious statement of what indices are needed. > > I don't think this can be done well by software. Of course, I haven't tried > it. > >> Has anyone figured out a good system for managing indices in a smooth, >> efficient, and reliable way in a non-trivial SQLite application? > > Sure. But you're not going to like it. > > General principles: > > A) Plan your schema properly. Tables represent things. Think through > COLLATE for every column, especially key columns. Use foreign keys rather > than copying data into other tables. No need to be obsessive about it but > "that's how it was done when I started here" is not good enough. Work out > your tables, your primary keys and your views and your indexes will take care > of themselves. > > B) If a query runs fast enough, it runs fast enough. Don't mess with "as > fast as possible". That way lies madness. > > C) Don't index a column just because it looks important. You create an > index, when you create an index, for a particular statement. You look at the > "WHERE" and "ORDER BY" clauses and figure it out from there. It's always > possible to create the best possible index for a statement by inspecting > those two clauses and thinking about how "chunky" each column is. You may > not need the full index -- the rightmost column(s) may be unnecessary -- but > it's a good starting point. > > Got the principles ? Right. Now here's the procedure: > > 1) Delete all indexes. > 2) Run ANALYZE. > 3) Run your application. > 4) Note the SQLite command which takes the most annoyingly long time. > 5) Work out a good index which will fix the problem. > 6) Create the index. > 7) Repeat from step 2. > > When your application runs fast enough not to annoy you, you're done. If > you're not willing to do step (1), don't bother with anything else. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
