In This lesion we’ll briefly define the concept and use of an index. An index is an additional feature that we add to a table to help us use that table more efficiently. It becomes part of the organizational structure for a table. An index can be used to assist in locating rows based on certain criteria as well as for ordering the rows retrieved in a result set. In general, an index has two parts, the value to be indexed, and a pointer to the row associated with that entry. The pointer might be the primary key value of that row or it might be an internal row pointer depending the storage engine used to manage the table. In either case MySQL handles the internal structure of the index. We simple tell mysql what column or columns should be used for creating the index. Contains of an index are maintained in the order implied by the data type of the column or columns on which the index is based. So if the index is based on a character string column, the index will be maintained in alphabetical order, if on a number column, then in numerical order, and so on.
Different storage engines offer us different indexing options. Most engines offers us at least B-Tree indexes which have a special internal organization that lens such an index to be especially useful in locating ranges of values. B-Tree indexes can be used with any basic data type. Some storage engines such as comma separated values engine or the archive engine do not offers indexing capabilities at all. This is not a fault or error on the part of the engineers who came up with these storage engines but rather a consequences of the intended uses of these storage engines. Here is a breakdown of some of the special indexing capabilities offered by certain commonly used storage engines.
- The MyISAM offers FullText and GIS indexes. Full text indexes are used for performing word searches while GIS indexes are used for the special type of data for finding locations on maps. Each of these requires columns with a specific data type. We can only have FullText indexes on character string columns and GIS indexes on special columns.
- The InnoDB storage engine uses B+tree indexes. This has a more efficient internal structure than the basic BTree index for certain operations but there is no difference in the way we setup a basic index with this engine.
The Memory storage engine offers us both hash (default) and B+tree indexes. A hash index is more efficient for finding an exact value but it’s completely useless for finding ranges of values.