Mysql How do you create a clustered index?
I'm reading all about how clustered indexes work, and think they would be beneficial to my app. I understand that primary keys are automatically clustered indexes, but how would you add a clustered index to a non-primary key column?
I.e. a datastore for user posts. Each post has a ID, but also has a user-id, but since users can post multiple times, the user-id is not a primary key. How would you add a clustered index to the user-id, and is that even a good idea?
Answer
According to Clustered and Secondary Indexes, you can have only one clustered index per table.
All indexes other than the clustered index are known as secondary indexes.
If a table has no primary index but another unique index, this is used as the clustered index.
If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
So, I would conclude, that you don't add a clustered index yourself, but MySQL chooses either the primary or the first unique index of a table as the clustered index.
If you haven't defined a primary or unique index, MySQL creates an index itself
If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.