How does InnoDB represent composite integer keys?

Question

Let's say I have the following table:

Create Table Orders(server : tiny int, ticket : integer, primary key (server, ticket));

How is the composite primary key representation when stored on disk?

1. server * 2^32 + ticket (a numeric composition of the two keys)
2. server & " " & ticket (a string concatenation of the two attributes)
3. Something entirely different

Answer 1

InnoDB uses B+Tree to store indexes and tables (the entire table is stored inside its primary key) and it stores the multiple columns as tuples (ordered lists) so actually there is somewhere an equivalent to a tree node having two properties - server and ticket both separate numbers.

The entire tree is then a way to store an ordered list of such pairs/tuples - they are ordered first by the first column (server) and in case of a tie the second columns (ticket) are compared and decide the ordering. For the primary key there should be no ties on both columns because it is unique, but for non-unique composite indexes there might be. But in InnoDB each secondary index has the primary key columns added to it (because the primary key is the pointer to actual data row) so any ties in there can be solved by ordering on the added primary key columns.

If you want to imagine it, then take a phonebook - each row consists of three values (lastName, firstName, phoneNumber) and is sorted in that order - the ordering might be effectively equivalent to lastName & " " & firstName but it is really done piecewise.