1

Primary key in relational tables. Composite primary key or unique primary key in those pure relational tables?

Which design would you recommend to use in MySQL for high performance? See diagram

Technical advantages and disadvantages!

Thanks everyone!

Emilio Nicolás
  • 2,554
  • 5
  • 22
  • 29
  • Very similar to http://stackoverflow.com/questions/6355479/mysql-autoincrement-compound-primary-key-performance-integrity/6356302#6356302 – Neville Kuyt Jun 16 '11 at 12:52
  • 2
    If you want performance, drop foreign keys completely. If you want proper database design and relation model - use the one that works without thinking about performance impact. – Michael J.V. Jun 16 '11 at 12:52
  • 1
    "If you want performance, drop foreign keys completely". That would be silly. Foreign keys can *improve* performance by improving the execution plan for some queries. In any case a schema without a constraint is not functionally equivalent to one with it so it's a bit arbitrary to start comparing performance between them. – nvogel Jun 16 '11 at 16:00

3 Answers3

2

It really depends on the type of query you're doing...

If you add an extra surrogate, you'll end up doing two unique checks instead of a single one for every insert, update and delete. That makes the composite key sound right.

But if you're constantly referencing that primary key in other foreign keys, the composite option means you end up storing twice as much information and need to do more work when joining. That makes the single key sound right.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
1

According to Elmasri and Navathe (in Fundamentals of Database Systems) you should choose option A because artificial primary keys are unnecessary and suggest that you have a denormalized design (their POV).

In the case of a join table, I happen to agree with the authors. Otherwise I think that in practice using artificial keys is perfectly fine.

I don't think the performance implications are tremendous on a join table.

Brian Driscoll
  • 19,373
  • 3
  • 46
  • 65
  • What if I want to sort this relational table by the order it was inserted (or DESC)? Is there a way without an INT PK for each row? – ijverig May 25 '22 at 18:50
1

I agree with @Denis, it'll depend on what you're doing. One other thing to consider is that the InnoDB will store the rows in PK order on disk. This is very important if you're doing things like id1 BETWEEN a AND b. Moving those read heads is about 10ms each time, and if the rows for your query are scattered, it'll add up. For exactly these reasons, you might consider denormalizing to putting the data you need in a single row.

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
  • link to proof that "innodb will store rows in PK order on disk"? doesn't sound like a reasonable thing for it to have to do. – ysth Jul 30 '13 at 18:46
  • InnoDB's clustered index is stored index + data in order: http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html – Joshua Martell Aug 01 '13 at 04:16