Solved

What is a Mysql Index for??

Posted on 2002-06-15
6
164 Views
Last Modified: 2011-09-20
Can you explain me what is an Index for? I know that is for getting faster queries but what do they really do?
0
Comment
Question by:matrix717
6 Comments
 
LVL 32

Accepted Solution

by:
Batalf earned 70 total points
ID: 7081420
An example: You have a table with these columns:

tableName : person

ID
lastname
firstname
country

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.

0
 
LVL 6

Expert Comment

by:andreif
ID: 7083222
0
 
LVL 4

Expert Comment

by:kalosi
ID: 7183652
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

              ROOT
             /    \
       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) .

0
 
LVL 11

Expert Comment

by:shmert
ID: 9643439
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.
               
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
               
Sam Barnum
EE Cleanup Volunteer              
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question