19 November 2008

My Top 10 List For Developers

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]

  1. DO know your tools, and do your testing on your own LAMP environment at your desk;
  2. DO document;
  3. DO ensure your queries use indexes and use them properly (there *is* a difference);
  4. DO understand the EXPLAIN statement - its benefits and shortcomings alike;
  5. DON'T use comma joins (you must explicitly help MySQL with execution plan on joins);
  6. DO normalize your tables for transaction processing; (I like 3rd normal, but it depends on the app);
  7. DO denormalize your tables for analytical processing; (the key is how much you denormalize!)
  8. DO NOT create indexes on columns with few unique values (like gender);
  9. DON'T open large recordsets (in a dev environment, this can be easily overlooked);
  10. DO always test with large datasets (that's why we have Dev, Test, and Prod servers!);
  11. DO always set numeric columns to NOT NULL unless NULL is absolutely required;
  12. DON'T use cursors (or better yet, skip stored procedures altogether and use an API based on php, perl, C, or python);
  13. DO understand the benefits and limitations of database abstraction and when to use it;
  14. DON'T oversize your varchar's, char's, and don't use TEXT type when other types will do.