Boost MySQL efficiency by adding an index.

[EricGregory]

One of our admins, Kevin Quinn took some time to do a nice write up about increasing the efficiency of MySQL by adding an index.  So far, it's been pretty well received.  We just posted it up yesterday, so head over and have a peek if you'd like.  As always, any comments or feedback are certainly welcome.

http://blog.totalserversolutions.com/boost-mysql-efficiency-by-adding-an-index/

Thanks

 

[raindog308]

The hard part is usually figuring out where to add indexes 

And of course, every index you add is a tradeoff: faster SELECT speed, slower INSERT/UPDATE speed (because each additional index has to be maintained).

 

[Enterprisevpssolutions]

Nice article need to add a little more info on how to fine tune your mysql and add indexes in certain sections.

 

[MCH-Phil]

A lot of good information about database design can be found by reading about database normalization.  More info:  http://www.phlonx.com/resources/nf3/

 

[marlencrabapple]

The hard part is usually figuring out where to add indexes 

And of course, every index you add is a tradeoff: faster SELECT speed, slower INSERT/UPDATE speed (because each additional index has to be maintained).

The tradeoff is minimal in just about everything I've ever written. I usually end up with maybe 3 SELECTs for every INSERT.

 

[jarland]

Good stuff. Over the last few years I've seen people go to incredible lengths, and costs, to compensate for insane MySQL usage rather than spend a single moment optimizing their queries. Just a few minutes running watch on "mysqladmin processlist" can change your perspective about website performance in many common scenarios.

 

[MCH-Phil]

Another amazing command that can help with SQL optimization is http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

If anyone has some real workloads or real questions concerning DB optimization or the likes, please let me know.