13 December 2007

My One Night mySQL Road Trip

I like driving. It relaxes me. I especially like driving in the Carolinas versus driving the freeways in Los Angeles. In LA, you have to intentionally calm yourself down before going to the on ramp of the 101, 5 or the 10. Here, in South Carolina, it is possible to drive highway speeds on the highways (in LA, it is possible, but only at night time or early in the morning).

When I heard that the Durham mySQL meetup had Jay Pipes over, I had to go. Thus, my Carolinas road trip, from Columbia, SC to Durham, NC. That was about the length of three CDs. Nice.

And, as always, when you connect with like-minded individuals in the industry, I *always* pick up on a few new techniques. It's like the same idea Sheeri talked about when seeing another side of things when in the presence of other mySQL DBAs.

As a result, this has become my list of "To-Do's" at our shop here. As each one gets done, I'll blog about it and turn each of these items into a link. So, without further ado, here goes:

  • Vertical partition infrequently updated fields in one table and frequently updated fields in another. We have a couple of tables that could use this, but the programming ramifications could be more significant than anticipated;
  • Remove redundant/poor indexes;
  • Think in terms of sets instead of loops - get comfortable using derived tables;
  • Take out non-deterministic values in our reporting queries
  • Turn MyISAM DELETES and UPDATES into batches
  • Convert "email LIKE '%website.com'" queries into "rv_email LIKE (REVERSE('website.com'),'%')" queries. Already did that today - worked like a charm! I'll blog about it tomorrow.
That's all for today!

Til next time,
David

1 comment:

Keith said...

David, you should really consider submitting your blog to planetmysql. There is a button or something right on the main page. You have a lot of good stuff on here that more people would know about if it was "on the feed".

Keith