High Performance MySQL

Having just finished off reading High Performance MySQL, I thought I’d write up a couple of things that I learned:

  1. Only one index is used per table per query.
    Meaning     that if you run a query like
    SELECT * FROM customers WHERE forename='David' AND surname='Carrington'
    and have two separate indexes on forename and surname, then MySQL will only use one of those indexes. The solution could be to create a compound index which encompasses both columns, but check out the second point I learned…
  2. A compound index is only used if the first column in the index is required in the query.
    Meaning that if I had created a compound index (forename, surname) then tried to do a query based on surname only, the compound index     would not be used! It would only get used if the index had been created the other way around.

Generally the MySQL I write doesn’t encounter these issues but I did find them both interesting, mostly because I’d never come across them before. There’s a few chapters I skipped in the book because I don’t (currently) need to know too much about them:

  • Load balancing – I leave that to our capable hardware team.
  • Replication - I know enough to get our implementation working.
  • Security – I think it’s fairly safe to assume that our existing infrastructure is quite secure, and I already have a basic understanding of permissions within MySQL enough to manipulate them using phpMyAdmin.

Overall, a very good read and highly recommended for those using MySQL.

I also have a digital copy of MySQL High Availability to read at some point, although I may focus on another area first.

Posted in Development, Review at August 24th, 2010. No Comments.

« Older Entries