Hyperion Reactor The Middle Child of History

MySQL by default will always use the best index for a SELECT. MySQL is not perfect and at times will mess this up by using a wrong index. In such cases you can force MySQL to use a specific index or otherwise ask it to ignore a specific index.

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;
 
SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

You can find more information here: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

I have been playing a lot with Drupal lately. I must say it does feel weird getting back to PHP after so much Ruby; but it is a refreshing change. One of the the things I noticed is that Drupal 6 adds a LOWER() to the column names when it does a select. Not that this is bad but then it confuses MySQL, and instead of using the "name" index it runs through the entire database. This is not a issue if your site has a few hundred users. But now consider a site that has about 5 million users (which is what I am dealing with)

I just finished hacking up this patch, that prevents it from adding the LOWER() to the column names. This boosted something as simple as the login process from 5 seconds to milliseconds. 5 seconds may not sound a lot to you; but then I use a Dell 1950 as my development box. Not that it is the best box in the world, but it is pretty powerful.

Last week David Axmark the co-founder and author of MySQL visited my office. Thanks to Sun for making this meeting happen. We spent some time discussing about MySQL and development of applications in general. These are some of the key "take-away" points from that meeting. A lot of these points may seem obvious, but a lot of companies decide not to implement them for various reasons.

MMM (MySQL Master-Master Replication Manager) is a set of flexible scripts to perform monitoring/failover and management of MySQL Master-Master replication configurations (with only one node writable at any time). The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP addresses around a group of servers depending on whether they are behind in replication.

This was developed by the MySQL High Performance Group at MySQL AB for a client.

DBAs have spent years developing their expertise in database management best practices, backup/recovery, performance tuning and writing really cool scripts. Today's successful DBA needs skills beyond just the management of their databases. These skills include:

• Operating Systems: Having sufficient knowledge in the operating systems their databases run in.
• Best Practices: Maintaining best practices for database management.
• Automation: Automating your database management.