Database tables with only one index ... bad idea?

Question

If a programming language allowed the creation of database tables, but only allowed one field to be used as an index ... how limiting would that be? I am not a real database programmer, so I'm wondering if such a system could be used as the basis for serious databases, or would be only a toy.

Answer 1

That would be extremely limiting. I've seen as many as 11 indexes on tables I've dealt with, all of them having a purpose. No doubt there are cases out there with substantially more.

At a minimum in a database the primary key and all foreign keys will be (or should be) indexed.

Also it's common for indexes to span multiple columns.

Answer 2

It depends. Berkeley DB e.g. only has a single key and is very useful for its purposes.

A relational DB on the other hand should give you the opportunity to define as many indices as you like (as cletus mentioned, foreign key queries would suffer from very poor performance otherwise)

Answer 3

Generally: an index is for speeding up database lookup: like in a book, you search the word "computer", you browse the (alphabetical!) index, and find all page-numbers.

If (very hypothetical reasoning) one creates a database which optimized lookup times without using indices: then such a database containing only one index is not a bad idea.

But if you're talking about a mainstream DB, then I'd say: depends on the content.

Answer 4

Non-relational databases (currently all the rage with the NoSQL movement) approximate such a situation. Many such databases are anything but toys, and are intensely used as the basis of hugely scalable production systems -- the lack of free indexing and other limitations empower almost unbelievable scalability, especially for "mostly-read" scenarios. The downside is basically in having to give up normalization, and duplicating information all over the place in ways that are strictly dependent on what queries need what degree of optimization.

I'm not sure what your "single index" scenario is based on, but unless it offers the same kinds of advantages as the best "nosql" storage systems (and you're willing to fully pay the price for that), it seems unlikely to be a technically viable concept;-).

Answer 5

It depends on what you are going to do with the data.

If you search and select based on an unindexed field, the process is going to be slow. Likewise, if you perform join or merge operations based on an unindexed field, you get a slow down.

If the table has millions of rows, this could be too slow to be acceptable.

Limiting yourself to access by way of just one field can in certain situations be too limiting.

Again, it depends on what you are going to do with the data.