How MySQL Uses Indexes

Some MySQL index Facts

  • MySQL indexes are used to find rows with specific column values quickly.
  • Without an index, MySQL must begin with the first raw and then read through entire table to find rows.
  • If the table has an index for the columns, MySQL can quickly determine the position to seek into the middle of the data file without having a look at all data.
  • If a table has 1000 raws, this will 100 times faster than reading sequentially.
  • Most MySQL indexes are stored in B-Tree.
  • The database stores the index value as a B-Tree key, and the record pointer as a B-Tree value.
  • Whenever you search for a record holding a certain value of an indexed column, the engine locates the key holding this value in the B-Tree, retrieves the pointer to the record and fetches the record.
  • What exactly is a “record pointer”, depends on the storage engine.
    • In MyISAM, the record pointer is an offset to the record in the MYI file.
    • In InnoDB, the record pointer is the value of the PRIMARY KEY.
  • In InnoDB, the table itself is a B-Tree with a PRIMARY KEY as a B-Tree key. This is what called a “clustered index” or “index-organized table”. In this case, all other fields are stored as a B-Tree value.
  • In MyISAM, the records are stored without any special order. This is called “heap storage”.
  • MySQL uses indexes for these operations :
    • To find the rows matching a WHERE clause quickly.
    • To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
    • To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size.
    • To find the MIN() or MAX() value for a specific indexed column.

I found these useful information on http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.

Advertisements

I like to hear from you about this !!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s