Re: How MySQL MYISAM manage the auto_increment column
Posted by:
Rick James
Date: November 19, 2011 09:15PM
Case 1:
PRIMARY KEY(grp, id) (and no other indexes)
-- Multiple rows can have id=1
Case 2:
PRIMARY KEY(grp, id), INDEX(id)
-- id will not start over; there will be only one id=1
"MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one."
Case 3:
PRIMARY KEY(grp, id), INDEX(name, id)
-- Multiple rows can have id=1, and they will be based on grp (I think).
"the AUTO_INCREMENT column is part of a multiple-column index"
This is too vague. Here's 5 examples of such:
PRIMARY KEY(id, x)
PRIMARY KEY(x, id)
INDEX(id, x)
INDEX(x, id)
PRIMARY KEY(x, y, id) -- third position does not work (I think)
Examples of "part of multiple indexes":
Example 1: PRIMARY KEY (x, id), INDEX(id)
Example 2: INDEX(a,b,c,id), UNIQUE(x,id,y)
Example 3: PRIMARY KEY(id), INDEX(id, q)
Think of the implemenation: In order to find what value to use next, it needs to efficiently do MAX(id)+1.
First choice: If there is _any_ kind of index _starting_ with id, reach into the end of that index.
Second choice: If there is a 2-column index, where the _second_ column is id, the do the grouping game. (InnoDB does not have this option.)
PRIMARY KEY implies UNIQUE. UNIQUEness is a 'constraint' that can lead to "duplicate key".
"any kind of index": I am referring to PRIMARY KEY, UNIQUE, INDEX, or KEY.
PARTITIONed tables add wrinkles to this discussion, but perhaps not serious wrinkles.
(Caveat: I thought I understood all this. But I had not discovered Case 2 until this forum thread. There may be other issues I am not perfectly correct on.)