Consider a table with the following columns:

name VARCHAR(64),
state VARCHAR(64)

We would like to partition the table on state, however that would require dropping the primary key constraint on phone and replacing that constraint with a UNIQUE constraint. We make use of INSERT ... ON DUPLICATE KEY UPDATE and I would like to know how it would affect the system. Surprisingly little is written on the subject from reliable sources, and I'm sure that there are hidden gotchas which will not be immediately apparent to my naive eye.

What problems might we encounter with a partitioned table lacking a primary key, especially in regards to INSERT ... ON DUPLICATE KEY UPDATE queries?

The database in question in running MySQL 5.5 on Amazon RDS. The table in question has upward of 20 million rows and is often used in JOINs with other similarly-sized tables.

  • What do you mean you have to drop the primary key? Show us the table definitions, both now and the planned with the partition. – ypercubeᵀᴹ Apr 29 '14 at 14:53

When we talk about partitioning tables in relational database platforms is recommended that it is always done on tables where the field is numeric. As a suggestion I would create an additional field that would serve as a surrogate key and this in turn would be used for partitioning the table.

| improve this answer | |
  • 1
    Really? Never partition on date columns? – ypercubeᵀᴹ Apr 29 '14 at 15:20
  • 2
    Could you please substantiate that claim? I've never heard it and I do not see why it would be valid. – dotancohen Apr 29 '14 at 15:28
  • Why was this downvoted? Could the downvoter elaborate, or provide another viewpoint? – dotancohen Apr 30 '14 at 6:17

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.