How many rows? If 1K, it does not matter. If 1 billion, we need to discuss in more detail. Let's assume 1M...
Dates should be stored in DATE datatype, which is 3 bytes. Your GUID is non-standard, but should probably be stored in CHAR(6) CHARACTER SET ascii, unless it is expected to have non-ascii characters. Is case folding important? Given those, the PRIMARY KEY is 6+3+3=12 bytes long. This is not bad for a million-row table. If bigger, then the AUTO_INCREMENT should be considered. Is this InnoDB or MyISAM? (Please provide SHOW CREATE TABLE, not English, for describing tables.) Let's see the SELECTs that will be hitting the tables. Then we can discuss in more detail. > -----Original Message----- > From: Mark Goodge [mailto:m...@good-stuff.co.uk] > Sent: Monday, July 16, 2012 8:09 AM > To: mysql > Subject: Unique index - opinions sought > > I have a MySQL table (call it, say, item_spine) which contains three > fields which, together, form a unique key. These three fields are a > guid, a start date and an end date. The guid is alphanumeric (a fixed- > length six characters) and the dates are ISO format dates (YYYY-MM-DD). > > I also have another table (item_detail) containing data which is keyed > to the first in that the unique key here is the unique key from > item_spine plus a line id (which is numeric). > > At the moment, I simply have the three fields in the item_spine set as > a unique key, and replicate those three columns in item_detail and have > those plus line_id as the unique key, thus making a four-column key. > > But, for performance reasons, I was wondering if it might make more > sense to create a single column in item_spine containing data which is > generated from the original three and use that as a unique key instead. > I then only need a single column in item_detail to link to item_spine, > and thus my unique key there can be only two columns. > > Another option is to have an autoincrement column as a primary key for > item_spine, and then use that as the link key for item_detail. But I'd > prefer to avoid that, because the content of item_spine has to be > updated on a regular basis from external data and using autoincrement > means I can't do that using REPLACE INTO while still maintaining a key > association with item_detail. > > Any thoughts? How would you do it? > > Mark > -- > Sent from my Turing-Flowers Colossus > http://mark.goodge.co.uk > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
