// 25.Nov.2009

Top 20+ MySQL Best Practices

Ah now this is pure gold, 21 things to keep in mind when coding for MySQL. Most of these I knew but it was good to be reminded. Some of them are entirely new to me though:

  • Use ENUM over VARCHAR: ENUM type columns are very fast and compact. Internally they are stored like TINYINT, yet they can contain and display string values.
  • Store IP Addresses as UNSIGNED INT: Many programmers will create a VARCHAR(15) field without realizing they can actually store IP addresses as integer values. With an INT you go down to only 4 bytes of space, and have a fixed size field instead.
  • Do Not ORDER BY RAND(): This is one of those tricks that sound cool at first, and many rookie programmers fall for this trap. You may not realize what kind of terrible bottleneck you can create once you start using this in your queries.

Priceless information. I'll be referring back to this list over and over again.

Last Revision: November 25th, 2009 at 12:45

2 Comments for “Top 20+ MySQL Best Practices”

  1. I always use INET_ATON() to store IP addresses.

  2. Any particular reason Noah? AFAIK INET_ATON stores 8-bytes addresses whereas with UNSIGNED INT you can halve that to 4-bytes.

    What would be the advantage of INET_ATON?