THe utility of Indexes

jonsof

Could someone explain to me the utility of having an index in a database?

ultraslacker

I'll answer your question by asking a question. What's the utility of having an index in a book?

Google is your friend.

jonsof

In a database, of course 🙂

xblue

let's put it another way:

the utility of having an index in a database is similar to the utility of having an index in a book.

now, ask yourself the question ultraslacker asked you, and that should answer it.

jonsof

it might be consindered something like an optimization of the database and improvement in seeking the approptiate data?

Sxooter

Exactly. If you want to find one row in a million, without an index, you'd have to look through every row. With an index you go right to the row you need.

But this brings up the point of query optimization. An index isn't always the best way to do things.

For instance, if you were wanting to read the whole book, it would be silly to use the index to look up every page, as you're going to read them all anyway. The same is true for databases. If you are going to grab 95% of all the rows with a query, then no need for an index, and the planner can just do a seq scan on the table.

finding that break point, where seq scan makes more sense than index is NOT an easy optimization, especially for complex queries.

jonsof

....and how can an index be implemented? What is the appropriate syntax to do that.

Sxooter

the exact syntax depends on your database, but generally the SQL standard format is something like:

create index index_name on table_name (col1);

for single column indexes, and

create index index_name on table_name (col1, col2, coln);

for a unique index (one that forces the column or combination of columns being indexed to be unique) would add the unique keyword:

create unique index index_name on table (col1);

Note that on postgresql there are a few extensions to normal indexes, like this, a functional index:

create index index_name on table (lower(name));

Or this, a partial index, useful when you only want to index a portion of a table:

create index index_name on table (col1, col2) where col3='y';

This index would get used in a query like this:

select * from table where col3='y' order by col1, col2;

csn

Hmm, but what about reading a book about databases? Read cover-to-cover, use the index, or use the TOC?? 😛

Sxooter

hehe. Funny, but think about it.

The index is like a normal index, every word on every page is indexed. The table of contents is like a partial index in that only pages that have the word CHAPTER XX or something are indexed, and the whole thing is a seq scan.