MySQL Indexing and Internals

A database index is a data structure that improves the speed of operations in a table. MySQL indexing is used to avoid full table scan.
An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table.

B- trees are the most commonly used data structures for indexes. The reason B- trees are the most popular data structure for indexes is due to the fact that they are time efficient – because look-ups, deletions, and insertions can all be done in logarithmic time. And, another major reason B- trees are more commonly used is because the data that is stored inside the B- tree can be sorted.

Source : http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work 

Here’s what the actual SQL would look like to create an index on the Employee_Name column from our example earlier:

CREATE INDEX name_index ON Employee (Employee_Name)

For more Read on :

https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

Advertisements

Leave a Reply

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