What is a Mysql Index for??

Can you explain me what is an Index for? I know that is for getting faster queries but what do they really do?
Who is Participating?
BatalfConnect With a Mentor Commented:
An example: You have a table with these columns:

tableName : person


Let's say you have thousands of rows with people from different countries. Now, if you want to search for a person from a specific country, you have to search through all the rows in the table. That is, if the table is not indexed.

If I add an index on the column "country", You can say that mysql adds a new column to you table, but this column is sorted on country.

Let's say that we have 5000 rows with people in our table, and 100 of these is from Norway. If you don't have an index, mysql have to search through all 5000 rows to complete this query:

- select * from person where country='Norway';

If I have added an index to the table(alter table person add index country(country) ), mysql uses the index and doesn't have to search through more than the 100 records where country = Norway. When it reads row no 101 which is not norway but a country later in the alphabet, it knows that there isn't any more records with country = norway.

Indexes is a big advantage and will speed up your queries. The process of inserting and updating values to the table will on the other hand take a little bit more time, because mysql have to update both the table and the index. My example above is perhaps not so good. The reason is that I'm using an index on a string-field which could be a bit long. The best thing is to add indexes to int-fields and other short values. mySql use more time to maintain indexes on larger fields.

Maybe you should read something about B-tree structures
theese are mostly used in every database (sql, ldap) and so on.

a database index is a b-tree which lookes like this

             /    \
       BRANCH1    BRANCH2
     /     \      /     \
   LEAFE1   LEAF2 L3     L4

assume that you a column in a table that contains a single
character . you have 100 records in the table (charachters from a-z)

if you submit a query (select * from table where column='a') the databse server must open the table and scan it row for row searching the 'a' character in the column (in oracle known as full table scan). if you create an index over that column the database creates a ballanced b tree structure over that column . assume that branch1 contains characters from a-e, branch2 from f - k ans so on
. the leafo objects contain pointers to the row addresses in the table. so the server opens the index, ans walks down the tree finding the 'a' character and then retrieves the row from the table using it's address (for example in oralce the is s special ROWID structure to identify the row in the table) .

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept: batalf
Please leave any comments here within the next seven days.
Sam Barnum
EE Cleanup Volunteer              
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.