An excellent question was posted in a MySQL group in LinkedIn the other day that I had to post it here.
What is your "Top 10 List for Programmers Working with MySQL"?
I started mine... Let me know your thoughts on my list, and feel free to add other items to the list. I interface with programmers on a daily basis, and our experience lends us to help them in certain ways, but I think a "Top 10" list will enable us to be vigilant in our day-to-day development interactions.
Oh yes - I have 14 items in my "Top 10 List", and I have no problem adding a few more... :)
Drum roll please... [cue David Letterman]
- DO know your tools, and do your testing on your own LAMP environment at your desk;
- DO document;
- DO ensure your queries use indexes and use them properly (there *is* a difference);
- DO understand the EXPLAIN statement - its benefits and shortcomings alike;
- DON'T use comma joins (you must explicitly help MySQL with execution plan on joins);
- DO normalize your tables for transaction processing; (I like 3rd normal, but it depends on the app);
- DO denormalize your tables for analytical processing; (the key is how much you denormalize!)
- DO NOT create indexes on columns with few unique values (like gender);
- DON'T open large recordsets (in a dev environment, this can be easily overlooked);
- DO always test with large datasets (that's why we have Dev, Test, and Prod servers!);
- DO always set numeric columns to NOT NULL unless NULL is absolutely required;
- DON'T use cursors (or better yet, skip stored procedures altogether and use an API based on php, perl, C, or python);
- DO understand the benefits and limitations of database abstraction and when to use it;
- DON'T oversize your varchar's, char's, and don't use TEXT type when other types will do.