Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know there are lost of similar questions to this one, but none that really answers this:

Is there ANY difference in performance in using an unique index in a primary key, using a MyISAM table on a server running MySQL 5.5?

If you need more informations, I can provide all.
I just want to know if it is worth (lets say, if the 'seek' time decreases by more than 5 milliseconds) to do that change or if I'm committing a hideous crime.

And yes, this is a serious question.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

A PRIMARY KEY is, by definition, UNIQUE and NOT NULL, so by adding a second UNIQUE keywork on it you are actually creating a different index, and that does not only make things more efficient but, in some cases, it will make your queries less performant.

You can check that two index were created by doing:

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `c` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Both in MyISAM and InnoDB, it will require additional space, it will make the writes slower (because the index has to be updated synchronously at write time), and it may waste optimizer (even if microseconds) time deciding which index is the best one. MySQL would usually select the PRIMARY key in MyISAM, but if for any reason both indexes were used, you will also waste memory.

If you do not believe me, believe MySQL:

mysql> INSERT test1 SELECT * from test;
Query OK, 1691648 rows affected (6.84 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE test2 add unique(id);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> INSERT test2 SELECT * from test;
Query OK, 1691648 rows affected (10.94 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

A run of pt-duplicate-key-checker is usually enough to detect those duplicates.

If you mean using UNIQUE NOT NULL instead of PRIMARY KEY, I would not expect a difference on MyISAM in most cases (although some tools work better with a PK, like row-based replication); it is not the case with InnoDB, in which the PK is fundamental on its structure (clustered rows around it).

share|improve this answer
4  
InnoDB will likely select the UNIQUE index over the PRIMARY KEY index in some cases (because it uses less memory). Cases like when only the key values are needed and not the whole rows. –  ypercube Sep 2 at 16:39
    
Thanks for the info and the SQL snippet. It has a nice explanation but there is nothing backing up your answer. And the memory is not a problem: I have 4GB to be used and I only use around 600MB on the server. (It's not required to, but I would like to see some time differences. Sadly, I can't test it right at the moment.) –  Ismael Miguel Sep 2 at 16:46
    
@ypercube You are 100% correct (and just confirmed it), I will edit the "MySQL would usually select the PRIMARY KEY" to add it is for MyISAM. –  jynus Sep 2 at 16:48
1  
@Ismael Miguel Of course, memory waste, disk space and optimizer may be difficult to prove (specially if you have a lot of those), but extra work for writes is as easy to see as inserting a million of rows and checking time differences. –  jynus Sep 2 at 16:52
3  
Then you have the answer by Jynus. Don't. It's useless. For a MyISAM table, you'll have basically 2 equivalent indexes. –  ypercube Sep 2 at 17:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.