7 ms·

A couple of points for those thinking about doing something similar: "Since InnoDB stores the table in the primary key, I decided that rather than use an auto_

A couple of points for those thinking about doing something similar: "Since InnoDB stores the table in the primary key, I decided that rather than use an auto_increment column, I'd cover several columns with the primary key to guarantee uniqueness. This had the added advantage that if the same record was inserted more than once, it would not result in duplicates." The 'correct' way to deal with this in MySQL is using the auto_increment_increment. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html http://dev.mysql.com/doc/refman/5.0/en/server-system-variabl... Of course, the real difficulty with mutli-master setups split across data centres isn't ensuring uniqueness of primary keys, it's ensuring data-integrity under a split-brain scenario, i.e. where one server can't reach the other, but users can reach one or the other. UPDATEs and DELETEs to rows can then become extremely difficult to merge back together. This wasn't a problem for this application, but as others have commented, this use case probably wasn't best suited for an RDBMS anyway.

bluesmoon 17y ago
with an autoincrement id, duplicate rows may get inserted. having a primary key derived from the data results in duplicate rows getting discarded (this is important). secondly, the autoincrement id adds 4 bytes to each row which are never used for anything. only use an id if you need to reference a row from another table.
- cperciva 17y ago
  duplicate rows may get inserted Make that duplicate rows WILL get inserted -- even if only due to network glitches causing connections to die after the database adds the row but before the client receives the acknowledgement resulting in the client retrying the request. Unless you don't retry failures, in which case you lose rows instead, of course.
- silentbicycle 17y ago
  That's a good warning sign that the data isn't relational in the first place. An RDBMS would probably be a good fit for the analyzed data (which is likely to have explicit relations), though.
  - encoderer 17y ago
    No it's not. There are plenty of tables that use composite keys that also contain foreign keys. The use of ORMs like active record, many of which choke on natural keys, has turned a lot of devs into automatons for artificial key creation. Natural keys are often superior.

bluesmoon 17y ago

with an autoincrement id, duplicate rows may get inserted. having a primary key derived from the data results in duplicate rows getting discarded (this is important). secondly, the autoincrement id adds 4 bytes to each row which are never used for anything. only use an id if you need to reference a row from another table.

cperciva 17y ago
duplicate rows may get inserted Make that duplicate rows WILL get inserted -- even if only due to network glitches causing connections to die after the database adds the row but before the client receives the acknowledgement resulting in the client retrying the request. Unless you don't retry failures, in which case you lose rows instead, of course.
silentbicycle 17y ago
That's a good warning sign that the data isn't relational in the first place. An RDBMS would probably be a good fit for the analyzed data (which is likely to have explicit relations), though.
- encoderer 17y ago
  No it's not. There are plenty of tables that use composite keys that also contain foreign keys. The use of ORMs like active record, many of which choke on natural keys, has turned a lot of devs into automatons for artificial key creation. Natural keys are often superior.