On 31 Jan 2014, at 4:41pm, Andreas Hofmann <andreas.hofm...@ku7t.org> wrote:
> I would like to fine tune table indexes. I want to make sure I got indexes
> for the columns or combined columns of all (most) where clauses. The issue
> is that the application builds dynamic SQL strings all over the place and it
> may not be easy to find them all without reading the code very closely. I
> would rather find out what indexes are needed/suggested by profiling the
> app.
>
>
>
> Is there a tool that can collect profiling information at run time and
> suggest missing indexes? If not, I am thinking about parsing the SQL
> strings myself, not a difficult thing to do, but I was wondering if I
> reinvent the wheel here.
One problem you're going to run into is that lots of optimizing 'wizards' start
off by building indexes based on what the schema is. They look at tables and
see what columns exist, and make up a few indexes which seem likely to help.
However, a properly defined schema already has indexes in for primary key
columns, FOREIGN KEYs, and for columns declared as unique. Sometimes this is
done inherently by the SQL engine, other times the SQL engine will complain is
such indexes aren't available. Those are vital indexes, but any decent SQL
programmer will have ensured they already exist. So at best, automated index
adders help only a little. And at worst they make database files huge and
increase processing times because every change has to updates many unused
indexes. I've seen some truly horrible examples, for various different SQL
implementations including some expensive ones that should know better. And I
once made a client's day simply by deleting a whole bunch of indexes which
speeded up their time-critical INSERT time, because their other operations were
run in an overnight batch and nobody cared how long they took.
The way to efficiently figure out what indexes should be created is to look at
WHERE clauses in all commands, and at JOINs and GROUP BY and ORDER BY clauses
in SELECT commands. Those are the things that actually /use/ indexes. You can
probably do that for some commands your app uses: look at each SELECT and
UPDATE and DELETE FROM command and figure out, for each table used, a single
index which would be 100% ideal for the command. Then eliminate or optimize
cases where two commands could use the same index, or where a long index
includes a shorter one.
However, you also mention that your app generates its own commands. Unless you
can predict things about these commands ("90% of the time users are going to
want to sort by date") you're not in a good place to figure out what indexes
are good. One solution in this is to add a routine to your app to log any SQL
commands it makes up, and how long the command takes to execute: create a log
table with a text column a numeric column. You can look at the results at your
leisure and try to improve on any command that takes more than 5 seconds, or
any command which is used a lot.
Lastly, for SQLite specifically, don't forget to run the ANALYZE command before
analysis and after creating or deleting indexes. This can yield dramatic
improvements.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
