Why most SQL databases allow defining the same index twice?

Question

Why most SQL databases allow defining the same index (or constraint) twice?

For example in MySQL I can do:

CREATE TABLE testkey(id VARCHAR(10) NOT NULL, PRIMARY KEY(id));
ALTER TABLE testkey ADD KEY (id);
ALTER TABLE testkey ADD KEY (id);
SHOW CREATE TABLE testkey;
CREATE TABLE `testkey` (
  `id` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `id_2` (`id`)
)

I do not see any use case for having the same index or constraint twice. And I would like SQL databases not allowing me do so.

I also do not see the point on naming indexes or constraints, as I could reference them for deletion just as I created them.

Answer 1

You shouldn't be in a scenario that you have so many indexes on a table that you can't just quickly look and see if the index in there.

As for naming constraints and indexes, I only really ever name constraints. I will name a constraint FK_CurrentTable_ForeignKeyedColumn, just so things are more visible when quickly looking through lists of them.

Answer 2

Don't really know for MySQL but is it possibly useful if the original index gets fragmented and you can build a new replacement one then drop the original one with minimal effect on concurrent readers?

Answer 3

All programming languages allow you to write redundancies:

<?php
$foo = 'bar';
$foo = 'bar';

That's just an example, you could obviously have duplicate code, duplicate functions, or duplicate data structures that are much more wasteful.

It's up to you to write good code, and this depends on the situation. Maybe there's a good reason in some rare case to write something that seems redundant. In that case, you'd be just as put out if the technology didn't allow you to do it.

You might be interested in a tool called Maatkit, which is a collection of indispensable tools for MySQL users. One of its tools checks for duplicate keys:

http://www.maatkit.org/doc/mk-duplicate-key-checker.html

If you're a MySQL developer, novice or expert, you should download Maatkit right away and set aside a full day to read the docs, try out each tool in the set, and learn how to integrate them into your daily development tasks. You'll kick yourself for not doing it sooner.

As for naming indexes, it allows you to do this:

ALTER TABLE testkey DROP KEY `id`, DROP KEY `id_2`;

If they weren't named, you'd have no way to drop individual indexes. You'd have to drop the whole table and recreate it without the indexes.

Answer 4

Several reasons come to mind. In the case of a database product which supports multiple index types it is possible that you might want to have the same field or combination of fields indexed multiple times, with each index having a different type depending on intended usage. For example, some (perhaps most) database products have a tree-structured index which is good for both direct lookup (e.g KEY_FIELD = 1) and range scans (e.g. KEY_FIELD > 0 AND KEY_FIELD < 5). In addition, some (but definitely not all) database products also support a hashed index type, which is only useful for direct lookups but which is very fast (e.g. would work for a comparison such as KEY_FIELD = 1 but which could not be used for a range comparison). If you need to have very fast direct lookup times but still need to to provide for ranged comparisons it might be useful to create both a tree-structured index and a hashed index.

Some database products do prevent you from having multiple primary key constraints on a table. However, preventing all possible duplicates might require more effort on the part of the database vendor than they feel can be justified. In the case of an open source database the principal developers might take the view that if a given feature is a big enough deal to a given user it should be up to that user to send in a code patch to enable whatever feature it is that is wanted. Open source is not a euphemism for "I use your open-source product; therefore, you are now my slave and must implement every feature I might ever want!".

In the end I think it's fair to say that a product which is intended for use by software developers can take it as a given that the user should be expected to exercise reasonable care when using the product.

Answer 5

Because databases that support covering indexes - Oracle, MySQL, SQL Server... (but not PostgreSQL, oddly). A covering index means indexing two or more columns, and are processed left to right for that column list in order to use them.

So if I define a covering index on columns 1, 2 and 3 - my queries need to use, at a minimum, column 1 to use the index. The next possible combination is column 1 & 2, and finally 1,2 and 3.

So what about my queries that only use column 3? Without the other two columns, the covering index can't be used. It's the same issue for only column 2 use... Either case, that's a situation where I would consider separate indexes on columns 2 and 3.

Answer 6

There are only two good reasons - that I can think of - for allowing defining the same index twice

1. for compatibility with existing scripts that do define the same index twice.
2. changing the implementation would require work that I am neither willing to do nor pay for