What is a database index?

[Barnaby W. Füi]

Doh, damn enter key

Anyways, I have been looking through google, and this seems to be one of those things where it's such a generic wording that you can't find what you're looking for. I know that indeces speed things up in a database - but how? What are they? What do they do? TIA

 

[BFG10K]

Indices are like pointers that store record locations. They're fast because the data is stored in a sorted order around that index so that you can go straight to where the record is based. On a non-indexed record the query has to search all of the data in a domain to find it because it doesn't know if it exists before-hand.

 

[kt]

Indexing is like organizing. Say you are looking for a particular record in a database without and index, it's like looking for a name in a list of names in no particular order. Wouldn't it be faster if your list of names are in alphabetical order? That's how your database engine will do it, it will index it first then look for your record. If you already have created an index for your table then your database engine wouldn't have to go thru that extra step of indexing the records and then look for the record you are requesting. But it goes deeper than that. Say you want to look for a particular name and age. Now you have 2 criterias. When you create your table, you may create an index based on two criterias.

CREATE INDEX lname_age_index ON tablename (last_name, age);

So, when you do a query like this:

SELECT * FROM tablename WHERE last_name='Smith' and age='16';

The database engine will use your premade index based on the two criterias you specified to speed up the search. I hope that made any sense.

 

[Barnaby W. Füi]

Thanks guys, I think I get it for the most part. Basically a database is normally not in any particular order and there is no way to know where to find something, so when you do a query it has to look through everything, but an index puts things in an order so that the db can tell exactly at which point something will be.

I can't totally picture how an index with two columns would work though.

 

[CTho9305]

The way I understand it:
It doesn't necessarily resort the whole thing into the index ordering - instead it keeps some lists (hashes, IIRC) so you can rapidly search for something. The index is kinda like a bunch of pointers that point to the actual location of the given items.

 

[kt]

No one said that indexing is sorting. It is kinda like in the old days when you go into a library to search for a book, you would go to the index cards (which are in some sort of order, ie: alphabetical) and each of the index cards contains the information for you to locate the book in the library. As for multi columns indexing.. just think of it this way. Say you look for a book and you know what it is about, ie: history. Then maybe the library has an index catalog based on subject. Then you'll be able to look for the book based on the subject and alphabetical order of the author's last name.

 

[Confused]

Given kt's explanation, i'll elaborate a little

In a library, you have the Dewey Decimal Classification listings, which give you a number, say 803.27. The 803 bit is the subject (say History), and the 27 is where within that bit, the book is located. The same would be true for the multi column listing. You'd sort by age first (given the examples above), then within the age, it would be alphabetically stored, and it will tell you exactly where to go to get to that certain record.

It's much quicker to go look in the library at the DDC listings, than to just walk in, and look round til you find what you're looking for


Confused