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 .