MySQL Development Best Practices

I strongly believe that MySQL programming best practices need to be known and followed by each developer, since practicing query optimization from day one make everyone's life much more easier. So I present here a very simple MySQL Development check list.


Check List for General Best Practices

1. Checking for indexing

1.1 All columns in the 'WHERE' clauses indexed?

• [Eg. select * from student where StudentId = '7986'; then column StudentId need to be indexed]
  

1.2 All columns in the 'ORDER BY' indexed?

• [Eg. select * from student ORDER BY dob; then column dob need to be indexed]

1.3 All columns in the 'GROUP BY' indexed?

• [Eg. select * from student GROUP BY Class; then column Class need to be indexed]
  

1.4 All columns used for joins indexed?

• [Eg. select country.Name, city.Name where country.Id = city.CountryId; both country.Id and City.CountryId are better to be indexed]
  

2. Checking for over indexing

2.1 No redundant indexes?

• when having composite indexes remember that the left most indexes can serve as indexing and does not require separate indexes.

• [Eg. select * from student where house = 'YELLOWS' and class = '3E';

• if student table has the following indexes a. index(house,class) b. index(house)The index(house) is a redundant index hence not required and better be removed.
  

2.2 No indexes that has been created and never used within the 'WHERE'or ORDER BY or GROUP BY or for joins?

2.3 No simple indexes created on column which does not have unique values?

• [Eg. A simple index on a 'sex' column with a data set with a distribution of 45% Males and 55% Females will not be usually used for result pruning]

3. Check for data types

3.1 Has the appropriate shortest data type been chosen for each column?

• [Eg. If a column can be defined as TINYINT do not have it as BIGINT]
• [Eg. If a column can be defined as varchar(10) do not define it as varchar(255)]
• [Eg. If a column can be defined as enum do not define it either as varchar, char or int]

3.2 Has the integer columns not taking minus values defined as 'unsigned'?

3.3 Has the columns which could never have NULL values explicitly defined as NOT NULL?

4. INNODB Specific Checks

4.1 No char columns in INNODB tables?

• [ Do not use char columns in INNODB always have VARCHAR]

4.2 Does All the INNODB tables have primary key?

4.3 Are the columns in the Primary key of the minimal possible length?

4.4 Has all the statements like, select count(*) from innodbTable; statements eliminated?

• [Specially do not use on critical tables with a huge dataset. As an alternative to the select count(*) use summary tables]
  

* NOTE: I had avoided complicated practices and included only the most simple practices.