Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
CREATE TABLE relations (

  object_id BIGINT(20) NOT NULL PRIMARY KEY,
  object_term BIGINT(20) NOT NULL,

  UNIQUE KEY link(object_id, object_term),

  CONSTRAINT fk_term FOREIGN KEY(object_term)
    REFERENCES terms(id) ON DELETE CASCADE ON UPDATE RESTRICT,

  CONSTRAINT fk_object FOREIGN KEY(object_id)
    REFERENCES objects(id) ON DELETE CASCADE ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I have a feeling that I'm indexing object_id3 times, and object_term two times. Am I right?

If yes, how can I fix that and still keep the constraints and force (object_id, object_term) to be unique ?


ok, second try:

CREATE TABLE relations (

  object_id BIGINT(20) NOT NULL,
  object_term BIGINT(20) NOT NULL,

  PRIMARY KEY link(object_id, object_term),

  CONSTRAINT fk_term FOREIGN KEY(object_term)
    REFERENCES terms(id) ON DELETE CASCADE ON UPDATE RESTRICT,

  CONSTRAINT fk_object FOREIGN KEY(object_id)
    REFERENCES objects(id) ON DELETE CASCADE ON UPDATE RESTRICT

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

is this better?

share|improve this question
    
Looks like a sound design to me. Stick with communicating the semantics of your design and let the engine worry about managing potentially duplicate indexes. Unless you have a specific performance concern then I wouldn't worry about duplicate indexes resulting from your constraint specification. –  Nick Chammas Dec 10 '11 at 0:38
1  
Your second try has fundamentally changed the meaning of the table. object_id is no longer guaranteed to be unique by itself. –  Nick Chammas Dec 10 '11 at 0:39
    
Actually I'm worried about the database size. From what I understand indexing a column multiple times increases db size, time needed to perform write operations... –  Alex Dec 10 '11 at 0:40
    
I realize now that object_id didn't need to be unique in this specific table because a object should be able to have multiple relations, so just the pair has to be unique. It's actually a good change :) –  Alex Dec 10 '11 at 0:42
2  
You mean remove the FK constraints? Not worth it. The database will always do a better job than you at maintaining referential integrity. :) Unless you have a known performance problem or expect extreme load against this table I'd say you don't have anything to worry about--your design is good. –  Nick Chammas Dec 10 '11 at 0:49

1 Answer 1

up vote 1 down vote accepted

object_id is indexed three times

  • PRIMARY KEY
  • UNIQUE KEY link
  • FOREIGN KEY fk_object

object_term is actually indexed once

  • fk_term
  • In UNIQUE KEY link, object_term is not the lead column.

UPDATE 2011-12-09 19:42 EDT

In the First try:

  • object_id is unique (PRIMARY KEY)
  • (object_id,object_term) is unique (this means object_id can only be associated with one object_term at any given moment)

In the Second try:

  • object_id is not unique
  • (object_id,object_term) is unique (this means object_id can only be associated with more than object_term)

UPDATE 2011-12-09 19:57 EDT

If you are concerned about diskspace taken by indexes, you have a decision to make

OPTION 1 : Diskspace is no object

This can only be beneficial if your queries effectively use covering indexes and you are willing to live with (and provide for) large diskspace.

OPTION 2 : Diskspace is a precious commodity

It's time to do some forecasting. Both object_id and object_term are BIGINT. You can cut space down some as follows:

SELECT * FROM relations PROCEDURE ANALYSE();

This will not dump all the rows. It will sample the data and come back with a recommedation for the best column definitions given the data.

Why is that important?

  • TINYINT UNSIGNED is one byte (Range 0 to 255)
  • SMALLINT UNSIGNED is two bytes (Range 0 to 65535)
  • MEDIUMINT UNSIGNED is three bytes (Range 0 to 16777215)
  • INT UNSIGNED is four bytes (Range 0 to 4294967295)
  • BIGINT UNSIGNED is five bytes (Range 0 to 2^40 - 1)

If you know the number will not surpass certain values, you can change the column definitions to accommodate less diskspace. At the very least, let PROCEDURE ANALYSE() suggest it for you.

OPTION 3 : Diskspace can be a bottleneck

The more house guests, the more housekeeping to accommodate. If a host can take care of 20 house guests, keep them all 20 happy, and the host is happy as well, the host can always care 20 house guests over and over again. Add one more guest, and the host's quality of services goes down hill. The same goes with indexes. If your queries are fast enough to warrant keeping indexes around, you need to select which indexes are really necessary, and eliminate indexes that are redundant (unwanted house guests). The host must make sure the each house guest's request (query) can be satisfied (done with covering indexes)

Nice Links on Covering Indexes

share|improve this answer
    
thank you Rolando ;) But if only lead columns are indexed from multicolumn keys, then what purpose serves a multicolumn key? Only to keep the pair unique? (i mean for example multicol primary keys) –  Alex Dec 10 '11 at 0:53
1  
increased time for write queries is actually a bigger issue. I guess one more index doubles the time needed to perform the query. –  Alex Dec 10 '11 at 0:58
    
Multicolumn Primary Key Provide Two Things: 1) Fast Primary Key Lookups. I once worked for a Company that uses Nielsen Ratings data with a Primary Key with 8 Columns in some tables, and ALL columns were used in queries. 2) You can speed up range queries (such as SELECT object_term FROM relations WHERE object_id=52;) and have the subsequent columns ordered for you, thus bypassing sorting operations. This made possible by index scans rather than table scans. This goes back to what I mentioned earlier (COVERING INDEXES). –  RolandoMySQLDBA Dec 10 '11 at 1:04
    
@Alex you know somewthing, you are right about increased time for writing. That would become OPTION 3. OPTION 2 may offset that somewhat, but OPTION 3 is indeed expensive. –  RolandoMySQLDBA Dec 10 '11 at 1:08
    
I also changed BIGINT(20) with INT UNSIGNED; I don't need that bigint, there's no way a table will exceed 1 mil rows –  Alex Dec 10 '11 at 12:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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