Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 12 down vote favorite
1

I am not so familiar with databases and now I am trying to understand the indexing mechanism.

From what I know, in a RDBMS, indexing on a column makes searching by that column faster. This is also true for the triple stores, only there indices assume you will search(for example) mostly by the subject, then by object and so on.

I am not sure about RDBMS, but on triple stores you can define more than one index, letting the store choose the best index for each query(hopefully I understood this right). Naturally, the following question appears:

Why shouldn't I add all the possible indexes to a triple store, and extending to a RDBMS, why not make indexes on each column(assuming I am not too lazy)?

share|improve this question

5 Answers

up vote 24 down vote accepted

Because, essentially, an index is an extra table, where the primary key is the field you're indexing and the only content is the primary key of your main table. So every update has to be replicated in every index that uses the field you update.

This is particularly noticable on Inserts. Imagine if every insert you did to a table had to be replicated on 20 other tables. It's going to be painfully slow.

Note that this gets even worse with compound, clustered and full-text indexes, but I don't want to complicate the issue for you yet.

share|improve this answer
up vote 3 down vote

The indexes are basically additional data structures which have to be built and stored. Building inde wastes CPU power (during writing operations) and storing it wastes disk capacity.

Why would you want to build and store indexes which you never use?

share|improve this answer
It is a purely theoretical question("what if/why not"). – Dragos Feb 9 '12 at 14:21
@Dragos I think the answer to those question is obvious from my post: If you did, every writing operation would get much slower and every record would waste a lot of disk capacity. Why not? Because CPU power and disk storage are expensive. – Matěj Zábský Feb 9 '12 at 14:24
I understand... Thank you! – Dragos Feb 9 '12 at 14:28
up vote 3 down vote

Only place indexes when needed. As a rule of thumb when I am developing a database schema, every table gets a PK Primary Key Clustered Index to start with. This will be the unique identifier for data in that table. In can be on 1 column or many.

After that, I usually just add Non-Clustered Unique Indexes on column(s) which I want to enforce uniqueness on.

This is the base schema. As the application gets developed and matures, we add indexes as needed based on performance concerns and how we are querying the data.

Every index added increases spaced used as well as adding additionally maintenance. So choose your indexes wisely.

share|improve this answer
While reading your answer, another question popped into my mind: Are Primary Keys usually automatically indexed, or do I have to specify myself that they will be indexed? Say, for example, in a MySQL database? – Dragos Feb 9 '12 at 14:32
Yes, a primary key should create a clustered index automatically for your (SQL Server). Only one primary key, thus only one clustered index per table. MySQL should be similar but maybe a MySQL expert can validate. – Jon Raynor Feb 9 '12 at 14:36
up vote 3 down vote

The strength of Indexes is that they are 1) a data structure that can be quickly searched through and 2) more compact than the actual tables, allowing more of the index to fit into memory instead of being paged to disk.

If you have an index on every column, then the indexes themselves will take more space than the table they represent. If the database really does use all of the indexes, it will require more time just to swap them in and out of memory. In addition, every index has to be updated on an inert, update, or delete.

Beyond that, indexes on a single column are not even the best you can do. Most relation databases actually allow an index on multiple columns, and the order of these columns matter. For example, if I want to search a database for all people who went to Duke from classes between 1980 and 1984, then what I want is an index on (School, ClassYear). The query would not be able to use an index with the same columns, but reversed.

So to create every possible index, there are at least n! ways to arrange columns in an index. With only 5 columns, there are 120 possible indexes.

Since there are so many possible indexes, you really do have to determine what indexes are useful for your application and create only those.

share|improve this answer
But would in your example two indexes: one on School and the other one on ClassYear be useful in any of the cases? – Dragos Feb 9 '12 at 14:50
@Dragos Sure, they can be. If I had another query that was only over Class Year (all students that went to a school in the class of 2004) then the Class Year index may be useful. Unfortunately, there are a ton of factors that the query engine uses when deciding what index to use when. If it turns out that half of the people in the database did go to school in 2004, then the database may just ignore the index and scan over the entire table anyways. If you want to get good at this, start using and reading execution plans – Chris Pitman Feb 9 '12 at 15:12
What I meant was, If I have separate indexes on School and ClssYear, would they be useful when searching for all people who went to Duke from classes between 1980 and 1984? – Dragos Feb 9 '12 at 15:19
@Dragos It depends on the specific db engine. For example, Postgres will use something called a Bitmap Index Scan in order to intersect the results of multiple indexes. It is up to the query engine to decide which index to use, and this will always be db specific. – Chris Pitman Feb 9 '12 at 16:23
up vote 3 down vote

Creating an index for every column in a table is usually a waste of space, and as others have mentioned, it can slow down insert/update operations. An index is used to speed up queries. I'd only recommend adding an index to a column if you notice poor performance when querying for values in that column.

Some databases may require an index for a table's primary key so you might not have a choice about that one. Also, if you have a very large text columns, there are specific technologies that are designed for full-text search and index, but they are not always the same kinds of index you'd use for a small numeric column.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.