Arsalan writes:
> > - look on your indexes, perhaps you can create an index on
> two columns?
> >
>
> Got them up the wazoo. Two column ones as well...
>
Make sure you don't have any you don't absolutely need. Each index you have
adds overhead to any DML statement as it needs to be adjusted.
> Just want to know, is an index on (foo,bar) different from (bar,foo)? Does
> the order in which they appear in the index creation statement and in
> subsequent queries make a difference?
Yes, very much so. An index on (foo, bar, qux) will only be used for queries
in which the WHERE clause contains one, two, or three of the fields starting
from the first (left). So,
"foo = ?", or "bar = ? AND foo = ?", or "foo = ? AND qux = ? AND bar = ?"
will use the index. The textual order in the query is irrelevant as long as
the fields themselves are there. These,
"bar = ?", "qux = ?", "qux = ? AND bar = ?"
will not use the index, and this
"foo = ? AND qux = ?"
will only partially use the index (for the foo lookup). Unfortunately I
can't explain it better, hence the long example. Also remeber the optimiser
will only choose a single index for use in a query and discard any others
you have. So plan wisely as you're balancing INSERT/UPDATE overhead with
SELECT speed.