?
Solved

What is a Mysql Index for??

Posted on 2002-06-15
6
Medium Priority
?
171 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
4 Comments
 
LVL 32

Accepted Solution

by:
Batalf earned 280 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

840 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