MySql Database Design Best Practices

1. Optimize Your Queries For the Query Cache

2. EXPLAIN Your SELECT Queries

3. LIMIT 1 When Getting a Unique Row

4. Index the Search Fields

5. Do Not ORDER BY RAND()

6. Avoid SELECT *

7. Almost Always Have an id Field

8. Use ENUM over VARCHAR

9.Get Suggestions with PROCEDURE ANALYSE()

10.Use NOT NULL If You Can

11.Prepared Statements

12.Fixed-length (Static) Tables are Faster

13.Split the Big DELETE or INSERT Queries

14.Smaller Columns Are Faster

15.Choose the Right Storage Engine

a)MyISAM Storage Engine

b)InnoDB Storage Engine1. Optimize Your Queries For the Query Cache

2. EXPLAIN Your SELECT Queries

3. LIMIT 1 When Getting a Unique Row

4. Index the Search Fields

5. Do Not ORDER BY RAND()

6. Avoid SELECT *

7. Almost Always Have an id Field

8. Use ENUM over VARCHAR

9.Get Suggestions with PROCEDURE ANALYSE()

10.Use NOT NULL If You Can

11.Prepared Statements

12.Fixed-length (Static) Tables are Faster

13.Split the Big DELETE or INSERT Queries

14.Smaller Columns Are Faster

15.Choose the Right Storage Engine

a)MyISAM Storage Engine

b)InnoDB Storage Engine

16. Use an Object Relational Mapper

17. Use well defined and consistent names for tables and columns

18. Use singular for table names . Table represents a collection of entities, there is no need for plural names.

19. Don’t use unnecessary prefixes or suffixes for table names .

20. Keep passwords as encrypted for security. Decrypt them in application when required.

21. Provide authentication for database access. Don’t give admin role to each user.

 22.Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis ...) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.

16. Use an Object Relational Mapper

17. Use well defined and consistent names for tables and columns

18. Use singular for table names . Table represents a collection of entities, there is no need for plural names.

19. Don’t use unnecessary prefixes or suffixes for table names .

20. Keep passwords as encrypted for security. Decrypt them in application when required.

21. Provide authentication for database access. Don’t give admin role to each user.

 22.Use an ORM (object relational mapping) framework  if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.