MySql Query Optimization Techniques and Good Practices

Several things need to follow at the time of creating the MySQL Tables and Query building.

1) In Select query don't  use the (*) asterisk symbol, instead of using the asterisk symbol  listed the database table column names. Why because select query follows the matrix mechanism at the time of retrieving data from the database table.

2) At the time of build the new Tables must follow the AUTO INCREMENT attribute. Here Auto Increment attribute consider, that column ID is primary key. Based on primary key easily perform the transactions.

3) Avoid to use "TEXT" and "BLOG" data types.

4) ORDER BY RAND() takes the much amount of time to retrieving the data. So try reduce the usage of RAND() attribute.

5) For good practice, table name columns use the  data type of the first character, Then easily identify, which datatype is the particular column at coding level.

Ex:
 iId - Represents the Integer Datatype Column
vTitle - Represents the  Varchar Datatype Column

6)  Don't use the much spaces for column size. We know what data we are passing that particular column, according to that we follow the datatype sizes.

Ex:
Country is the one of the column in our database. Here we need maximum 50 characters only. So we create  varchar country(50) its  enough.

7) Enumeration Data type is one of the most important fast retrieving technique. Instead of using the Integer we try to use enum datatype.

8) For any MySQL attribute , use the capital letters. It is also one of the good practice.

Ex:
SELECT,  INSERT, DELETE, FROM, OR, ORDER BY, LIMIT etc.

9) For single column data retrieving use the LIMIT 1. If we use this, looping is stopped, once that  particular column was detected.

Ex:
SELECT iId,vTitle FROM student WHERE iId = 1 LIMIT 0,1;