What is the indexing?

Feb 21 2014

What is mysql indexing? To understand this let’s take a very simple example from real life. A phone book has thousands of phone numbers, and we want to search John’s number. Then we simply go to the page where “J” is first character. After that we check the second character and third and so on. By this process the number can be found in short period. Now that if the number would not have been sorted, then every record/name was needed to be checked and then it will become an lengthy and time consuming process. Indexing also works on same principle.

Now the second point that comes in mind is that why we don’t index all the columns of a mysql table? There are two main reasons for this. First is, It will require extra space to store data of indexes. Second and very important reason is that, it will slow down the insert/update queries as every insert and update query will also need to update the indexes thus adds up extra overhead.

Indexes require a lot of space for performance. A phone book table having 2 billion rows in it, adding an index on name will require a lot of space. If the average name is 8 byte long, mysql need 16 GB space for indexes. Luckily mysql provides full control on indexes. We can index only first 4 byte instead of full index. This is called as partial indexing.

“Alter table phone_book add index(name(4))”

Mysql also provides multiple column indexes. It is very helpful when we can use two or more columns into same query. For example firstname and lastname for phonebook. Multiple column indexing are also referred as composite indexing or compound indexing. Why don’t use separate indexing on firstname and lastname? There is a very simple reason to this. Mysql will ever use only one index per table per query. So in case of separate indexing mysql either uses indexing of firstname or of lastname.

Mysql indexes aren’t always used to locate matching rows for query. We can also use them as constraints. For example unique indexes. Unique indexes ensure that each value of a column appears only once.

With MYISAM tables, the indexes are kept in a completely separate file that contains a list of primary key and a value that represents the byte offset for the record with clustered indexes the primary key and the records are all stored in primary key order. When your data is almost frequently searched via its primary key, clustered indexes make lookups incredibly fast.