DallasPHP Strategic MySql
I attended the DallasPHP group last night. The topic was “Strategic MySql” which was well presented and concise. Here’s what I took away from the presentation/discussion:
- ‘Limit’ statements do not reduce the size of a query. Instead it fetches the complete result set, and then serves the limited portion to you.
- Rather than deleting data with one-to-one queries, consider marking your data “to be deleted” and handling it in a single automated query.
- A utility table containing aggregate information like row counts is a great place to store and retrieve commonly accessed database information without the need for a high overhead query.
- MyISAM is 10-40% faster at writing than InnoDB, but is not as flexible.
- Mixing table types (MyISAM/InnoDB) based on the type of data they’re holding might sound like a good idea, but the extra overhead of handling two table types cancels out the benefits. Best to choose one and stick with it throughout.
- Storing IP Addresses as a numeric value using inet_aton() is a far better idea that a 14 character varchar() field.
- I need to learn more about something called MySql Slow Log.
- Definitely need to look at something called “memcache”. Was described as “the easy button”.
- Benchmarking tools to Google: MySqlslap and ApacheBench
- Monitoring/reporting tool to Google: sqlyog
- MySql caching is turned on by default in my.conf.
Those are my notes – hope you found them useful. Some things I need to do more research on, others are just tidbits I might not have thought to look up on my own.
This is actually the most valuable part of any developer’s group for me. Without other programmers to talk to, I’m essentially developing in my own bubble. It’s one thing to go and look up an answer to a question, but it’s another thing entirely to know which questions to ask.