Optimizing MySQL Database Design and Queries
The situation
So the question was like this: "What do you know about SQL and optimizing MySQL? Normalization? SQL Queries?
My response was something like this: "I am very familiar with SQL; Joins, Subqueries, Operations, Conversions. Yes I am familiar with normalizing data base designs and identifying indexes, keys and foreign keys."
Now you have to understand that this was one question among many in a phone interview, so I wanted to answer with enough key words to let the interviewer know how familiar I was with the topic without going in to exhaustive detail on the subject.
If I was to restate my answer I would have talked more about identifying bottlenecks using profiling and performance measurement tools but not much more. However if I get called back I think I will go into more detail on these and the above topics.
I could go on and on about these topics myself but in a quick search I found a post on a blog by Jesse Farmer that covers all the general strategies. Anything more would be specific to an implementation.
10 Tips for Optimizing MySQL Queries (That don’t suck)
In summary:
- Benchmark, benchmark, benchmark!
- Profile, profile, profile!
- MySQL Slow Query Log
- EXPLAIN
- SHOW STATUS
- SHOW PROCESSLIST
- Tighten Up Your Schema
- Partition Your Tables
- Don’t Overuse Artificial Primary Keys
- Learn Your Indices
- SQL is Not C
- Understand your engines
- MySQL specific shortcuts
- INSERT … SELECT
- INSERT … ON DUPLICATE KEY UPDATE
- REPLACE
- Be cautious with INSERT DELAYED
- Read Peter Zaitsev’s MySQL Performance Blog
Updated: 06/09/2009 A recent A List Apart article: Indexing the Web—It’s Not Just Google’s Business also covers many of these questions at a more "laymens" level with a focus on Indexes.
Conclusion
I have used most of these at one time or another, some frequently and others sporadically. In my experience it all depends on the project. Not all projects are equal or require equal amounts of vigilance. Custom one off applications with a limited lifespan probably can do without any optimization other than a good schema, depending on the expected traffic of course. Any application that is expected to go for multiple years requires optimization and periodic review. Any application that will be shared with the world for unknown uses really should have the best possible schema and really good starting optimization and instructions for maintaining it. OTOH you can not know the end user's needs, environment, traffic patterns or level of experience with database administration, so there is only so much you can do to give them a good starting point.
Re-read the above list, follow the links and continue exploring. Database design and optimization is a fascinating topic for those interested in relational datasets and software (web or otherwise) application design.