Combination primary key VS. secondary key in MySQL

Question

I have a table with two specific columns, one is an automatically increasing digit, the other is a user number. These two columns will be extracted together at the place where it is often. Now I have 2 options with the InnoDB engine:

• Use an automatically incrementing number as your primary key by creating an index in the custom number column. this choice will be very quick when pasting, because the new record will be inserted one after the other, and this will lead to some merging or splitting by the cluster index. But creating an index in a user number column will take extra time and space.

• I can use auto-increment number and custom number columns together as primary key. When you insert, the record will not fit one after the other, so it will result in splitting the main merge and merging the main tree B.

These two options will both speed up a query that uses an auto-increment number and a column number as a filter condition. But if we look at insertion and space, which would be preferable?

Thank you very much!

Answer 1

In this case, the composite primary key seems to be better as the key becomes the coverage index. And why do you think it will require splits? The auto-increment field must be the first in the composite primary key in InnoDB, that is, all new rows will be added to the end.

And the auto-increment field in InnoDB is unique throughout the table, even in a composite primary key, unlike in MyIsam, where it is restarted for each new part of the remainder of the primary key.