Mysql – Does making a field unique make it indexed

constraintderbyMySQLunique-constraint

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly accessible?)

Specifically, I'm working with Apache Derby for prototyping, although I will probably be moving it to MySQL in the semi-near future. I'm also hoping there might be something in the SQL standard that says something about this.

I will never have a need to search by this field, so I would rather not make a useless index. But I'd rather have a useless index than have an O(n) insert time.

Best Answer

--EDIT--

My original answer (below) is probably not useful to you at all because it does not address the question of unique constraints. As others have said, these constraints are usually implemented with an implied unique index. In special cases this might not be true (eg disable novalidate for Oracle).

The question could be: Is it possible to enforce uniqueness without an index? Generally speaking the answer is no though in some cases a Clustered Index will mean that the index and the table are the same object.

--END EDIT--

You said "I'd rather have a useless index than have an O(n) insert time.", but in general databases do not have O(n) insert time. There are two cases to consider:

A normal table with or without indexes:

New rows are dumped at the top of the heap. The RDBMS probably only looks at 1 block, so not just O(1) but very small O(1).

If the table has indexes, a pointer to the row will be added to each. This will usually be an O(log(n)) operation.
A table with some sort of clustering going on, eg an Index Organized Table or cluster for Oracle, or a Clustered Index for SQL Server and others:

New rows are inserted into a particular block, which may cause the block to split or overflow, but whatever happens it is still O(log(n)) or better, caused by the b-tree or similar structure used to find the block.

Related Solutions

Mysql – Unique insert performance: Primary Key vs Unique Index

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be. With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case. If you were using InnoDB, however, there would be a difference since it stores the data in primary key order. If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count. The difference in the counts would be the same as the affected rows. However, I don't think this would be significantly faster, but it would make the process more complex.

Postgresql – check key if exists in other table without fk constraint

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

Best Answer

Related Solutions

Mysql – Unique insert performance: Primary Key vs Unique Index

Postgresql – check key if exists in other table without fk constraint

Related Question