High Performance MySQL
Having just finished off reading High Performance MySQL, I thought I’d write up a couple of things that I learned:
- 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…
- 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.