data base optimization

* Why we need optimization of our SQL queries ?
Simple you may feel you sql query executes with in few seconds even though u have not used any optimization principles.But think of a situation a busy server which has to execute thousands of queries concurrently.So here Milli second delay also will be a reason for a dead lock. So it is highly recommended to use dead data base optimization principles.

here i am going to discuss how to use indexes to optimize sql queries.
Simply an index is a key which helps to find a particular value with out searching entire table of a data base.There can be more than one values which are mapped to a particular key(Index).Lets say we are finding a value from data set which is generated by a join operation as follows.

SELECT name,price form item i,dealer d WHERE i.id = d.item_id;

here join operator produced huge resulting data set.So if id and item-id are not indexes we need to search through that huge table and it is a useless time consuming process.But if above variables are indexes mysql has to check only index tables and then directly get the mapping rows in the tables.

Some important things that we need to think when we create an index for a table

* indexed a column that you will need it for a validation.(where id = 1000, ordered by name )

* you index should be contains very few records.(if it contains large number of records it is not worth to search though the index table since it has similar number of data like in the value table)

* columns with enum data type is not recommended to indexed.because that column can contain fix number of variable records.(same as declare in enum type). So there can be many rows with the same index.So no point to have large number of records for a indexed column as i mentioned above.

* index should be short one.Because MYSQl supports indexed caching to reduce number of look ups..If the indexes are too long(data size) it cannot caches large number of records.so we cant get the maximum use from the indexed caching .(if the index field is too large we can use prefix index to reduce the length.)