Solved

What is a Mysql Index for??

Posted on 2002-06-15
6
165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do I show metrics with PHP or Javascript 6 46
JQuery Search Filter 2 71
deprecated mysql extensions 1 29
Find RGB colors from a screen. 2 19
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 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