Solved

What is a Mysql Index for??

Posted on 2002-06-15
6
162 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
Comment Utility
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
Comment Utility
0
 
LVL 4

Expert Comment

by:kalosi
Comment Utility
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
Comment Utility
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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 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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now