Now if You run Query : EXPLAIN select * from tests where name='jadu' then MySql will scan/search 'jadu' from entire rows(4 rows) like :
But after Applying index(create INDEX index_1 on tests (name)) on name column Mysql will scan/search 'jadu' from 1 row(s) instead of 4 like:
Where should we apply INDEX :
1. Use INDEX in your WHERE clause by which you are retrieving rows.
2. Use INDEX in primary, foreign keys filed
3. use INDEX on the fields by which you are joining tables etc...
Advantages :
Through your application, normally users will fetch/browse data with more more time than insert/update/deleting operation. So MySql will take less less time to search/browse data.
Disadvantage: When users insert/update/delete then MySql will take more time to index/process your data ready for searching
But search/fetch is 80% and insert/update/delete is 20% in application, So why not indexing?
