You are invited to Log in or Register a free Frihost Account!


What is index in database design? I basically can`t understand what is it and the function of it. Is it same with primary key or some candidate key?
It's always a index on the primary key but it's not the same thing. Indexes makes it much faster to look up data. Imagine the following query:
SELECT * FROM table WHERE year=1987

If there is no index it has to go through all the rows in the table to see what tables are from year 1987. If it's very many rows it's not hard to realize that it can be very slow. If you have an index on the column year, it will be much faster. It will only have to look at the index to see what rows has year 1987. This is much faster look up.

On the downside it will slow down inserts and updates on the index column. Indexes also take up some disk space but it's often worth it.

It's possible to use indexes on more than one column. Good if you often have two columns in the where part.
Adding an index on a field will (usually) allow you to search that data faster.
However, because the index needs to maintained for every record, it will slow down insertions and deletes for all record fields and slow down updates on that particular field.

Essentially an index is a separate ordered list of every item in that field. It's (usually) much quicker to search something which is ordered because you don't have to search every record for equality and ranges of values.
Index (SQL Server 2005):

- Improve query performance by reducing I/O operations (It doesn't have to search the data so much in the hard drive)

Index in the text book. It lets you find the exact location of what you are looking for in that book without you browsing the entire book.

- Indexes are built on one or more columns in a table or view

- Improves the performance of SELECT, UPDATE and DELETE.

Why index can improve SELECT, UPDATE DELETE?
- Index improves INSERT, UPDATE, and DELETE operations because the database needs to find the data BEFORE doing any actions with it.

- Hurts the performance of insert operations.

Why index hurt INSERT?
- The database will have to find a way to FIT the new data within the index.
Related topics
where does index.htm go?
Problems With Index Page
Can't access .TK domains
Not Changing Index Page Often Increased (Page)Ranking 4 SE ?
I need help setting up a default index file?
Problem z index.html
PHP index page not displaying problem
how many clicks on FRIHOST Forum Index - before you give up
Default index.html
PHP-Nuke Installed, frih's index loading
why is my url for my index page so long? take a look
how to change the default index page
Index page load order
How does one link back from forum index?
Reply to topic    Frihost Forum Index -> Scripting -> Others

© 2005-2011 Frihost, forums powered by phpBB.