Single index database tables ... bad idea?

Question

If a programming language allowed you to create database tables, but allowed to use only one field as an index ... how will this limit? I am not a real database programmer, so I wonder if it is possible to use such a system as the basis for serious databases or to be just a toy.

Answer 1

That would be extremely restrictive. I saw as many as 11 indexes on the tables that I reviewed, and they all have a purpose. Undoubtedly, there are cases when they are much larger.

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

Multiple columns are also common for indexes.

Answer 2

Non-relational databases (currently all the rage with the NoSQL movement) is approaching this situation. Many of these databases are nothing but toys, and they are intensively used as the basis for extremely scalable production systems - the absence of free indexing and other restrictions gives almost incredible scalability, especially for “mostly read” scenarios. The disadvantage is mainly in refusing to normalize and duplicating information everywhere in ways that strictly depend on which queries need to what degree of optimization.

I'm not sure what your single index scenario is based on, but if it does not offer the same advantages as the best nosql storage systems (and you are willing to pay the full price for it), this is unlikely to be a technically viable concept; -).

Answer 3

It depends. Berkeley DB, for example. It has only one key and is very useful for its purposes.

On the other hand, a relational database should enable you to define as many indexes as you like (since cletus mentioned, foreign key queries will have very poor performance otherwise)

Answer 4

As a rule: the index speeds up the database search: as in a book, you search for the word "computer", you look at the index (alphabetical!) And find all page numbers.

If (a very hypothetical reasoning) a database is created that optimizes the search time without using indexes: then such a database containing only one index is not a bad idea.

But if you are talking about the main database, I would say: depends on the content.

Answer 5

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

If you search and select based on a non-indexed field, the process will be slow. Similarly, if you perform merge or merge operations based on an unindexed field, you become slower.

If a table contains millions of rows, this may be too slow to be acceptable.

Restricting access to only one field may be too limited in certain situations.

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