Rue: Use MEDIUMINT for storing user Id for intranet applications. You can store -8388608 to 8388607 signed values or 0 to 16777215 unsigned values.
Rue: You can consider using BIGINT UNSIGNED for storing user Id for internet based applications. You can store -9223372036854775808 to 9223372036854775807 signed values or 0 to 18446744073709551615 unsigned values.
Rue: Always use UNSIGNED numeric types unless you want to store negative numbers.
Rue: Always have an primary key id column which is one of the INT types and UNSIGNED.
Rue: Use ENUM type over VARCHAR for storing predefined string values, e.g. "inactive", "inactive", "male", "female" etc.
Rue: Use DATE type instead of DATETIME if you want to store only date.
Rue: Use VARCHAR for variable string values.
Rue: Use INT UNSIGNED for IP4 values.
Rue: Using NOT NULL saves 1 bit per column.
Rue: Use binary type to store md5 value with 16 bytes instead of 32 bytes of varchar type.
Rue: Consider fixed format table structure (no varchar, no blob or no text columns) for tables with more write operations.
Rue: Use LIMIT in your SELECT, UPDATE statements if you already know your are looking for only one match, e.g. while checking for username and password match.
Rue: Avoid SELECT * FROM and specify only the interested column names.
Rue: Use index only when needed, indexes are good for reading and bad for storing data quickly. Identify the proportion of read and write operation on every single table in your database.
Rue: Consider storing image data outside the database and store only the reference of the image in the database. You can reconsider this approach if you want to support replication of servers.
For more information please refer the following sites,http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
http://dev.mysql.com/doc/refman/5.1/en/optimization.html
http://www.slideshare.net/ronaldbradford/top-20-design-tips-for-mysql-data-architects-presentation
http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/
No comments:
Post a Comment