I have a junction table like this:
CREATE TABLE `entitytagjunction` ( `EntityId` bigint(20) NOT NULL, `TagId` bigint(20) NOT NULL, PRIMARY KEY (`EntityId`,`TagId`), KEY `IX_EntityId` (`EntityId`) USING HASH, KEY `IX_TagId` (`TagId`) USING HASH, CONSTRAINT `FK_EntityTagJunction_Entities_EntityId` FOREIGN KEY (`EntityId`) REFERENCES `entities` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_EntityTagJunction_Tags_TagId` FOREIGN KEY (`TagId`) REFERENCES `tags` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
TagId as composite primary key to ensure uniqueness, and SQL Server and MySql will both use the primary key as clusteredId.
As far as I could read from the other posts, Guid is not good to be a primary key because the randomness make fragments for clusteredIds, poor for performance if the table consist of millions of rows. Apparently the combination of
TagId has some randomness. Will this have impact on performance when using them as a composite primary key and clusteredId by default? or should I add an int column as a surrogate primary key though it will not be used in applications?