On Wed, 2003-10-15 at 09:25, Paul J Stevens wrote: > Caveat: I'm no sql guru. > Rule of thumb: all fields used in where clauses should be indexed.
Not exactly true... Every time you create an index it slows down inserts and updates (again something that will be helped with the new phy_message table). So there is a tradeoff in performance. Also depending on the type query and the data being returned the database may choose not to use an index for performance reasons. So if you just go ahead and create indexes everywhere you may wind up slowing down alot of operations without any select benefit. That said, I use dbmail on PostgreSQL and blindly adding a bunch of indexes improved perforance tremendously, so it still may not be optimal, but much better than the default setup. > What I did back in april was check for select queries logged in syslog, > look for where clauses, and cut-n-paste those to a mysql command prompt > preceeded by 'explain'. FYI, PostgreSQL 7.4 (due out in a few weeks) has an option to log all queries that take longer than X milliseconds to run. This will be helpful in identifying slow queries. > I have not studied the code to look for all possible select queries, so > I may well have missed an indexable field or two. Right, just as significant as indexes could well be optimizing the SQL code. > I was somewhat surprised by this lack of optimal table structure in the > default installation. I would expect IC&S to have seriously optimized > their tables since they claim to run installations with 10th of > thousands of mailboxes. Me too.
