You can create an index on a database table:
create index index_name on table_name (field(5));
You can check your index with this command:
show index from table_name;
or
show index from dbase_name.table_name;
show index from table_name from dbase_name;
As you can see, MySQL shows a lot of information about the index.
After creation of the index, you can check the .frm file (more information)
The table in this example has a primary key (PRIMARY appears in the .frm file). There's also an index (blabla)
You can also check the .myi file to see how the index looks like:
Offset 1025 of the .myi file shows the beginning of the indexed data
As you can see in the output above, the index is in B-TREE data structure. B-tree is a tree data structure that keeps data sorted and allows searches, insertions, and deletions in logarithmic amortized time. Unlike self-balancing binary search trees, it is optimized for systems that read and write large blocks of data. It is most commonly used in databases and filesystems.
More information:
http://en.wikipedia.org/wiki/Btree
More information about MySQL indexes:
CREATE INDEXES:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
SHOW INDEXES:
http://dev.mysql.com/doc/refman/5.0/en/show-index.html
Comments