Conecuh Software

Date: 23-Jul-2007
Time: 8:30 - 12:30 PDT
Presenter: Jay Pipes

Great information on MySQL tuning. I generally pull on my extensive Oracle experience when writing queries for MySQL. Lots of generally good practices work for both Oracle and MySQL, but, as I learned today, there are some “gotchas” for MySQL that need to be accounted for. My main take away for this tutuorial is as follows:

• The MySQL EXPLAIN command is pretty useful for determining how well your queries are going to run, and what path they are going to take. They’re useful if (and only if) you understand the myriad of values for the type, key, ref, and Extra fields in the output.
• Range access rocks in MySQL. This is one of the operations that MySQL has been optimized for, and was originally intended for.
• select * from table is a bad idea in MySQL, as it generates a full table scan. Always specify the columns in the select clause.
• When you see eq_ref in your MySQL EXPLAIN, that’s a really good thing.
• OR causes a full table scan on MySQL <= 4.x
• Don’t use subqueries in MySQL < V5.2.4. The optimizer doesn't generate optimum SQL for these. I guess it's generally good to re-write your subqueries as joins or whatnot, since that's what the optimizer is going to need to go anyway
• InnoDB is highly optimized. I need to research how well it works for my particular problems, though. It’s probably a better fit than MyISAM
• MySQL QUERY CACHE is a good thing, but only on heavy read databases. The databases should be more than 80% read.
• MySQL QUERY CACHE is invalidated for tables where updates happen. It’s many times better to normalize out frequently updated columns (ex. page_views) into another table, and pull that back directly, instead of letting the update of the frequently updated columns invalidate the QUERY CACHE.
• The “invalidation” of the QUERY CACHE can cause a bunch of thrashing, and it may be better to turn off QUERY CACHE instead of having all of that thrashing going on for light read applications

This entry was posted on Monday, July 23rd, 2007 at 2:30 pm and is filed under OSCON07. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.