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.
One thing I've been curious about is why SQLite doesn't provide more powerful
help here. A few things I can think of that would be useful would be:
o Some kind of "gee, I sure wish I had this index" info from the query
planner.
o Index utilization statistics, so little-used or unused indices could
be identified and eliminated.
o Timing info for each step of the query plan (and for that matter the
overall query), so query profiling would be easier and you could quickly
identify the problem spot in a complicated query instead of having to
constantly play find-the-peanut.
I'm no database engineer, so I have no idea how easy or hard these would be!
Randall.
> From: Simon Slavin <slav...@bigfraud.org>
> 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
