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
