We had the Top 5 wishes for MySQL started by Jay recently. So in true chain letter fashion I’m starting a new one this week. “The top 5 Best Practices for MySQL”. This like it’s predecessor is generally vague, so it can include points on development, design, administration etc.
My list:
-
Write your application to support Transactions (and therefore use a Transactional Storage Engine).
-
Always use SQL_MODE. e.g. at least TRADITIONAL and ANSI to ensure better data integrity and errors as errors.
-
Use the most optimal data types (particularly for number (e.g. TINY/SMALL/BIG INT and nullability) and especially in relation to columns in indexes.
-
When using InnoDB use the shortest primary key possible (e.g. INT UNSIGNED. BIGINT unless you have more then 4 billion rows in your potential data set is laziness).
-
VARCHAR(255) is just plain dumb and lazy. This is not database design and for the record, yes there is an impact when your queries use certain buffers (e.g. sort_buffer). Last year I wrote on this topic in If you don’t know your data, you don’t know your application. . Combined with SELECT * FROM TABLE in queries is not a well designed application.
Obviously I need to clarify that this is a baseline for Best Practice and many considerations can lead to a more optimized means depending on circumstances, for example using MyISAM or other MySQL non-transactional storage engines etc, when not to use sql_mode etc and when the shortest Innodb is not the best when you are being killed by I/O. For points 3 and 5, there are no exceptions.
To all Planet MySQL bloggers and readers, the challenge is on.
