Return applicable data columns in SELECT queries
Retrieve information via SELECT * become a habit of all developers. Unfortunately, the SELECT * query will return data columns which are not necessary on the application side, thus this action will waste server resources and consumes extra bandwidth. It is better to return required data columns to the application than to do SELECT *.
Choose the correct datatype for table fields
MySQL provide a handy function – analyse() to anaylse your existing table. This function will return following report.
field_name – The name of the returned column
min_value – The smallest value in the result set for this column
max_value – The largest value in the result set for this column
min_length – The smallest number of bytes in the result set for this column
max_length – The largest number of bytes in the result set for this column
empties_or_zeros – The number of returned rows with either an empty field or zero value in this column
nulls – The number of returned rows with nulls in this column
avg_value_or_avg_length – For numeric columns, the average value in the result set for this column; for string columns, the average length of all the values in this column for the result set
std – The standard deviation for the result set for this column, assuming it is a numeric column
optimal_fieldtype – The MySQL-preferred data type for this column, based on the data sample contained in the result set
Example command – SELECT * FROM test PROCEDURE analyse(4);
This command allows you to analyze your data and make further decisions to review the existing column datatypes for optimization.
Use Constraints to improve performance
MySQL intro following type of constraints for INSERT,UPDATE, or DELETE statements
1) UNIQUE
2) PRIMARY KEY
3) FOREIGN KEY
4) DEFAULT AND NOT NULL
5) ENUM
6) SET
When Not to Normalize
Another trap that overzealous database designers fall into is the practice of creating lookup tables for columns that have fewer than five different values stored in small columns. This commonly happens for columns that store information from small potential pools, such as eye color, gender, seat preference (for example, window or aisle), and so on. These kind of join will add more complexity to the application, although this is technically a violation of good database design theory.