I want to create a MyISAM like behavior for an InnoDB MySQL table. I want to have a composite primary key:
PRIMARY KEY(id1, id2)
Where id1 auto increments based on the value of id2. What's the best way to accomplish this with InnoDB?
+----------+-------------+--------------+
| id1 | id2 | other_column |
+----------+-------------+--------------+
| 1 | 1 | Foo |
| 1 | 2 | Bar |
| 1 | 3 | Bam |
| 2 | 1 | Baz |
| 2 | 2 | Zam |
| 3 | 1 | Zoo |
+----------+-------------+--------------+