On Wed, 14 May 2008, Michael Mayer wrote:
In contrast, storing the messages in a very ineffectively clustered way, like
it currently seems to be, really costs read performance. It's not that there
are not enough indexes, but you must also use the right ones. It seems like
you prefer to define an infinite amount of indexes in the hope that you hit
the right one sooner or later ;)
In general, I find that defining suitable indices for all the frequently
executed queries is a pretty good starting point. If the performance isn't
holding up, the chances are that there is an unindexed query somewhere
that is causing the problem.
Another point is, that the MySQL optimizer might not be as mature as the one
of Oracle (which should be pretty sophisticated) - it makes mistakes
sometimes and especially choosing the right index can be a difficult task.
While I acknowledge that there is the occasional bug that causes the
optimizer to get it wrong, it has been a fair few years since I've run
into such a condition on MySQL or PostgreSQL. And the problem isn't
generally that hard - using the index with the highest cardinality of the
available ones is a pretty good heuristic.
There are also a couple of other things to consider:
1) IIRC, MySQL will not bother looking up the data page if all the data it
needs is available in the index already, so pruning the selected fields
can be useful.
2) I'm not sure if this is still the case, but a while back MySQL couldn't
use multiple indices per table per query. That means that there was
performance could be improved by making a multi-column index. Ordering of
the index is also important here. The first element should be the one that
is often used on it's own (a multi-column index can be used when only the
first column is required for look-up), and as a secondary optimization,
the ordering should be in the descending cardinality order.
Having said that, by the time you getting to this level of optimization,
you are definitely in the diminishing returns territory. Single-column
indices on frequently used fields will generally get you most of the way
there in the first pass.
Gordan
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
