Home / Indexes / Question No: 135

I am new to MySQL and need to learn more about this database. I want to know about MySQL indexes. Can you explain, what is index terminology or concept in MySQL? Also, why the indexes are needed to speed up select queries?

An INDEX is essentially an ordered (or indexed) subset of table columns, with each row entry pointing to its corresponding table row.

We index the specific columns to keep them in organized versions. An indexed column just provides a sorted data structure so that some searches on that column could be faster. For example, a telephone book. It's in sorted order by name. So it makes it a lot faster to find someone by name. So a telephone book is a form of an index.

MySQL allows to create indexes to facilitate quick retrieval of desired data. Using indexes, MySQL can jump directly to the wanted record. Without using index(s), MySQL has to read the entire data file to find the required record(s). In short, with INDEX select queries work considerably faster and without INDEX such queries works slow. Take an example, create a table called "buyers":

CREATE TABLE buyers(buyer_id INT NOT NULL, buyer_name CHAR( 50 ) NOT NULL );

Then insert 1000 buyers with different names into the table in a completely non-alphabetic order. A small portion of the data file may be represented like below:

buyer_id buyer_name
 1        Zeshan
 2        Aqeel
 3        Nick
 [...]    [...]
 998      Lara
 999      Barbara
 1000     Ken

By above there seems no recognizable order to the "buyer_name" column. However, if we create an index on the "buyer_name" column, MySQL will automatically order this column's data alphabetically:

buyer_name
Aqeel
Barbara
Ken
Lara
Nick
[...]
Zeshan

In case of indexed column, for each data entry, MySQL also internally maintains a "pointer" to the inserting row in the actual data file. So if I want to get the buyer_id of Lara like

SELECT buyer_id FROM buyers WHERE buyer_name='Lara';

MySQL can jump directly to the correct row in the data file using buyer_name index "pointer", and return the value of buyer_id (998). So with the index MySQL only has to look at one row to get the wanted result. Without an index on "buyer_name", MySQL would’ve scanned all 1000 rows in the data file to find the wanted buyer_name 'Lara'. In other words, with the index(s) MySQL has to evaluate very less rows to respond the query.

In short, indexes are one of the keys to get speedy responses in large databases. It does not matter how simple the table is, the scan will never be fast when a table has 500000 rows. If a dynamic site has 500000 rows table, the developer should really analyze possible indexes and possibly has to rewrite queries to optimize the application.