Geek asks and answers

MySQL defines a column as UNIQUE using AUTO_INCREMENT instead of the primary key

I work with a contract developer who starts creating tables (MySQL) without defining a Primary Key. Instead, it defines a column with a UNIQUE constraint using AUTO_INCREMENT. I had never seen this before, so I wanted to understand the implications of defining tables this way. One drawback will not be able to create foreign keys if necessary.Some other consequences, good or bad, for creating tables this way. Maybe I'm missing a clue here ...

+3
source share
3 answers

Defining MySQL without explicit primary keys is a very bad idea.
If there is no PC, MySQL will create an implicit (but very real) integer primary auto-increment primary key.
This PC will be included in each optional key in InnoDB and will determine your primary sort order in MyISAM.

Consequence
You have just slowed down the performance of each selection, insert, and update.
For something wrong.

InnoDB: Extra Search Needed to Retrieve Table Data
In InnoDB, you need to perform an additional search because all secondary indexes refer to the PC, not the rows themselves.

MyISAM:
MyISAM , - 4 , .

InnoDB + MyISAM:
, ; , 1, 2 .

InnoDB:
, , , InnoDB , .

InnoDB:
InnoDB, .
MySQL , , , . , 50% InnoDB - , .

, !
enter image description here

:
http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/ ( , ).
O'Reilly InnoDB http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

BTW, , , MyISAM, , InnoDB, . InnoDB , MyISAM , .

+6

, , . , (, WHERE).

0

- , NOT NULL, , , , , , .

-1

More articles:


All Articles