Is automatically indexing primary key really good?

Question

In some DBMS like MySQL the primary key is always indexed by default. I know indexing can make operations like selection and comparison of the indexed column much faster, but it can also slow down other operations like insertion and update. There are cases when there are few selections on the primary key of a table, in which indexing will not bring much benefit. In such cases wouldn't it better not indexing the primary key?

Clarification: I just learned that primary key is actually implemented by a special index, like clustered index for InnoDB. Index can definitely be used to enforce the uniqueness constraint of primary key, but is it really necessary to use index to do this? From what I know, index is often implemented as btree which can improve the performance of many more operations than just checking the uniqueness, which can be simply done by a hashtable. So why not use other simpler structures to enforce the uniqueness that have less negative impact on the performance of insert and update operations?

The article here mentions a similar point:

Unique indexes use as much space as nonunique indexes do. The value of every column as well as the record's location is stored. This can be a waste if you use the unique index as a constraint and never as an index. Put another way, you may rely on the unique index to enforce uniqueness but never write a query that uses the unique value. In this case, there's no need for MySQL to store the locations of every record in the index: you'll never use them.

And in the following paragraph:

Unfortunately, there's no way to signal your intentions to MySQL. In the future, we'll likely find a feature introduced for this specific case. The MyISAM storage engine already has support for unique columns without an index (it uses a hash-based system), but the mechanism isn't exposed at the SQL level yet.

The "hash-based system" is an example of what I meant by "other simpler structures".

Answer 1

Let's go back in history about 20 years when MySQL was just getting started. The inventor said to himself, "what indexing system is simple and efficient and generally useful". The answer was BTree. So, BTrees are all that existed for a long time in MySQL. Then he asked himself "what bells and whistles should we put on the PRIMARY KEY". The answer was KISS -- make identical to other UNIQUE indexes. This was the MyISAM engine.

Later (about 15 years ago) another inventor joined forces. He brought 'simple', yet transactional, InnoDB engine. Since transactions really need a PK, InnoDB has a PK that is UNIQUE and clustered. And, again, the data+PK is a BTree.

Every so often someone would ask "Do we need bitmap indexes, hash indexes, a second clustered index, etc." The answer always came back, "No, BTree is good enough." A few non-MySQL engines have been invented to do non-BTree indexes. Perhaps the most successful is Tokutek and its "Fractal index". MariaDB now includes TokuDB. Another is the "columnar indexing" of Infinidb.

(Apologies to Monty and Heikki if they did not actually ask those questions.)

Hash and BTree indexes are about equally fast for "point queries". But for "range queries", Hash is useless and BTree is excellent. Why implement both when one is clearly better?

Answer 2

A primary key that isn't indexed is neither primary nor even a key.

Your question doesn't make sense.