Use Of Indexing In Mysql

Indexing a column in mysql is sorting that column according to its datatype and adding its corresponding primary-key as an extra information . Its like a map with key as column value and value as primary key sorted based on column value . Generally the primary key is to identify a row. since indexes has primary key associated,it is easy to identify records on the index order .

Indexes consumes memory to store column-value and primary-key value in pre sorted order . Although indexes consumes memory it will be very useful when reading a row with respect to indexed column. Since data are already pre-sorted,sorting overhead during read (file sort and temporary sort) is avoided and it also facilitates easy retrival of a data with respect to a criteria on the indexed column . Mysql allows 64 indexes including primary and foreign key in a table .

Now we see how indexing improves performance of a select query having join and order by in mysql select query . Consider two tables Table A , Table B . Both tables have three columns col1,col2.col3 . col1,a auto increment column,is primary-key for both tables , col2 of table B refers col1 of table A . Dump both the tables with million rows .

Now execute a select query like select * from table A inner join table B on tableA.col1=tableB.col2 order by tableB.col3 and look at the query plan . You will find temporary and filesort are used to give actual result . Now Index the table B's col3 and see the query plan you will find sorting overhead eliminated,index used and the data retrival time fast .

Try yourself and check . Please keep the quote "use indexes wherever needed and optimize query" in mind while you index a column unless it will lead to unnecessary data storage . I will write about how mysql executes order by in next post .

Innodb and Myisam Engines in Mysql .

Mysql has three major storage engine so far . Myisam , Innodb and falcon (released from mysql 5.1.14) . Though falcon is easy to use but it has shown many performance degradation in its beta release . So we focus on innodb and myisam engines .

Myisam engines is the default engine in mysql . It was built with ISAM (indexed sequential access method ) . Innodb is the next engine where all used to say performance in innodb is more when compared to myisam .

Regarding to performance, innodb performs well because it has better caching system . It has clusterd index to store data ( i.e data of the table is stored in the leaves of the primary key ). You have to go through lot of secondary indexes and retrive a primary key in innodb . Even though clustered index is fast , it will come at the expense of the memory . Since it store data in the leaves of the primary key memory used for caching a table will be high . Myisam engine is not clustered indexed . Myisam stores only primary key and retrives data of the table with respect to key . With this approach there will be no memory issues but the speed innodb retrives table data is more when compared to myisam .

For any online services, availablity is more important . Innodb is crash safe when compared to myisam . Innodb supports transaction while myisam does not support transaction . Myisam writes table info in .frm files and data in .myd file and indexes in .myi file while innodb has only bin log file . So during crash innodb easily recover by replaying its bin log file . Myisam also recovers from crash but it is acheived with cost of time .
For a real time mysql server there will be GBs,TBs of data , in this case innodb recovers more faster than myisam by replaying its log files and also we can get data which was commited before crash using innodb since it flushes data to log file after each transaction commits .

Coming to concurrency innodb supports row level locking when a table is queried with respect to primary key then only the row in that particular table is locked instead of locking entire table . Innodb also suuports mutex locking . There is some threshold limit of update transactions in innodb . Since myisam does not support transaction , it does not have any overhead like this . Large number of threads entering innodb at a particular time may lead to thrashing . So innodb user must keep this in mind and plan for transaction integrity . Overall concurrency will be more in innodb if table locking is avoided instead row level locking is made . This table level locking may lead to dead lock when large number of threads try to acquire lock for a single table .

The above things seems to be a paid advertisment for innodb but it is not like that . Myisam is very useful for insert only and less select tables . For example audit and stats in application . Since auditing a application has lot of inserts and less select one can use Myisam engine for audits and stats . So your application should define which engine is used for which module .

Now in mysql 5 onwards there is maria search engine which is developed by the author of myisam engine . This is released and not stablized yet . Hope this will solve most of the needs , the main aim for the authors are to support both transaction and non-transaction modes , multi version concurrency control , enhanced indexing model with no clustered index , larger page size , flexible memory allocation . If these things are stablized then most of them will be using maria in future .

Dhoni - A Real Captain Of India

I am very much admired with dhoni's performance as a captain . At initial stages of his carrer as a cricketer he hit ball very hard (even now he hits hard) and most of the deliveries he hit went over boundary of the cricket ground . He usually draws attention of the audience by hitting sixes and fours and became famous soon . For everyone who become famous will struggle to maintain their fame , but he managed to maintain it even during his fallback (especially during world cup ) . In my point of view he has no fallback till now because i saw him as a wicket keeper till he became captain of INDIA . He kept wickets well during world cup also , he showed improvement in keeping wicket but his weak point in batting were identified by other teams simultaneously . He worked hard to overcome those week points soon after he was bowled in a same passion for many times .

He usually stays cool in critical situation and thinks of what to do next . I think his hard work and attitude admired BCCI , so he was promoted as indian captain all of a sudden . I felt angry over BCCI when he was promoted as indian captain because Rahul Dravid ressigned as indian captain because of his promotion . I like Rahul very much . He is my cricket idol . Later i realized dhoni is doing better than rahul . When i was watching india-pakistan league match of world twenty20 championship , things appeared different in field set up and bowlers were given more freedom to set their field and bowlers were bowling with no internal pressure . Usually captain decides who to bowl a over according to situation and same thing happened with a different way . Bowlers who have less experience were performing well . That time i understood he boosted confidence of all and made all to realize whatever happens that happen and we try to win until last ball of the match . He did that too . Usually he comes to a match with a plan and he knows how to change that plan according to situation . His brain works more faster than a computer to decide things when his initial plan fails .

He also imporved his batting after becoming a captain . He was playing according to situation , if he need to stand and score then he scored like Rahul Dravid and if he want score more then he scored like Srikanth . I have nearly 10 role models in different aspects . His decision making made me to take him as my role model in decision making . He deserves of being a role model for many . I watch him in each and every match and learn something from him . Infact he made a normal team with less batsmen, no strike bowlers and no efficient fielders to enter finals in IPL2008 . This proves his efficiency .

All say when sachin play god watches his game , but i say when dhoni captains indian team sachin watches his game as a captain . Dhoni should be proud to captain a team which has players like rahul , sachin , sourav especially sachin , he handles them well too . In any time of match he is ready to accept the advice given by any of his team mates , this proves his professionalism . Like most of dhoni fan i also wish him best of luck and hope to continue his captaincy as good as now .