4

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;

I have EntityId and 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 EntityId and 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?

migrated from stackoverflow.com Jul 12 '15 at 14:37

This question came from our site for professional and enthusiast programmers.

  • When you start talking about GUIDs I assume you are thinking of adding a surrogate key to the table, dont. As Zohar says in their answer, you will need to enforce uniqueness anyway and a composit primary key does that. The clustering of the key is a separate issue. – Tony Jul 12 '15 at 19:07
  • As you have a GUID FK, you probably have a GUID PK in the referenced table - so if you want to add some autoincrement to use, you can do it there and keep the GUID as sort of a handle. That one table will probably be more important, this one will be quite small (you can pu a lot of bigints to 1GB). But if you use the GUID everywhere, having autoincrementing key would only slow everything by adding one more layer to every access. – jkavalik Jul 12 '15 at 19:26
  • The best design pattern. – Rick James Jan 1 '16 at 5:14
1

You should use the combination of EntityId and TagId as composite primary key, here is why:

  1. This will enforce uniqueness, that you will have to do even if you add a surrogate key.

  2. These columns are what you will base most if not all of your queries on this table, so they should be indexed anyway

  3. Adding a surrogate key will not have any benefit from the performance perspective.

btw, I don't know about MySql, but in Sql Server, a primary key is not necessarily the clustered index. It is set as the clustered index by default, but you can change the clustered index to any other index if you need.

  • but a clustered index is still needed, right? based on a unique column? – ZZZ Jul 12 '15 at 12:07
  • 1
    a clustered index is not necessarily a unique index... You will need a unique index to prevent duplicated data, and it should be clustered as well to enhance select performance. AFIAK, The clustered means that the order that the records are actually saved on the disk is the same order as they are saved in the index itself. – Zohar Peled Jul 12 '15 at 12:27
0

If entitytagjunction is a simple junction table that is never referenced, then you can make the two columns a primary key. This solves the problem of creating a unique index on the two columns.

If other tables could be referring to a row in entitytagjunction, then don't use a composite primary key. Composite primary keys just complicate foreign key references, SQL queries, and can sometimes become unmaintainable.

I strongly prefer auto-incremented int or bigint as single-column primary keys in tables. These are efficient for indexes. They are easy to reference as foreign keys. They provide information about the ordering of rows. For a clustered index, they are easy, because they always go on the last page. The latter is a slight downside compared to GUIDs, because they have slightly more information about each entity. Admittedly, in a simple junction table, they are also redundant.

Note that in MySQL the primary key is automatically the clustered index. Hence, you do not need an additional index on the first key in the primary key. In your example, KEY IX_EntityId (EntityId) is redundant, unless you really care about the performance difference between a B-tree and a hash table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy