Last week I summarized the Percona Live 2012 Conference for you. I went over some of the different topics that were covered during the talks. Today I’m going to briefly discuss the 2 tutorials that I personally felt were most beneficial to me and hopefully they will be to you too! Both talks were designed for Database Administrators (DBA) but could be easily appreciated by application developers and Systems Administrators as well. Ronald Bradford (Effective MySQL) was the speaker at both talks made them feel like a 2 part series. I found the information in “Idiosyncrasies of MYSQL That Bite” to build on the first talk, “Explaining the EXPLAIN”.
Explaining the EXPLAIN was the first talk of the two. Ronald Bradford used this presentation to discuss how the MySQL Query Optimizer develops a Query Execution Plan (QEP). He pointed out that EXPLAIN will only work on SELECT statements. This means you may need to re-write a query into a SELECT using WHERE. It’s interesting to note that the QEP is generated every time MySQL actually processes a query. There is no way to guarantee that a query is run in the same fashion as the QEP provided using EXPLAIN but chances are good. I learned that based on how your query is formed and how your tables are built, there are times where MySQL might just completely re-write your query to get the results you are looking for, and the resulting queries could be very expensive.
The second talk was called Idiosyncrasies of MySQL That Bite, also by Ronald Bradford. A lot of what he talked about here was actually familiar to me, but there were some extremely valuable insights that came from his time working with databases in general. For instance, I knew that the default date for a DATETIME column was all zeros, but I never considered how badly this could affect critical data where MySQL has replaced malformed dates with the default. I also never thought about how a misspelled “`ENGINE=InoDB`” or charset mismatch could completely change the way perfectly normal queries could run. Luckily, MySQL has the SQL_MODE variable which can be used to modify most of this kind of behavior.
Both talks were valuable to me, partly because in each one, the speaker showed us some very interesting quirks to look out for in MySQL, and what to do when you see them, or how to even prevent them all together. He did it in a way that was fun, and easy to understand. I hope you were able to take away some tips as well.