Do we need to index foreign keys?

pearl2 · #1 (**permalink**) 03-31-09, 00:29

Hi,

Sorry if this question is a bit silly...

For simplicity, let's say I've the following 2 tables:

Code:

CREATE TABLE users (
  id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL DEFAULT '',
)

CREATE TABLE points (
  member_id MEDIUMINT(8) UNSIGNED NOT NULL,
  points MEDIUMINT(8) UNSIGNED NOT NULL,
  INDEX (member_id),
)

Is there a need to index the foreign key "member_id" in the "points" table?

Thanks in advance

guelphdad · #2 (**permalink**) 03-31-09, 09:40

Yes you should index it since you will be most likely joining on it.

You should also actually declare it as a foreign key because right now it isn't one and you could have values for member_id that don't exist as id in your users table.

also note you need innodb table types to enforce foreign keys. you can declare them in myisam tables but they aren't enforced.

pearl2 · #3 (**permalink**) 03-31-09, 11:57

Thanks, guelphdad

I wasn't sure because I thought once it's indexed as a Primary Key in the "users" table, it need not be indexed elsewhere.

I've reproduced my sample tables - in my haste, I forgot about using InnoDB.

Code:

CREATE TABLE users (
  id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL DEFAULT '',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE points (
  member_id MEDIUMINT(8) UNSIGNED NOT NULL,
  points MEDIUMINT(8) UNSIGNED NOT NULL,
  INDEX (member_id),
  CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Are the new (albeit reduced) tables correct?

r937 · #4 (**permalink**) 03-31-09, 12:11

Quote:

Originally Posted by pearl2

Are the new (albeit reduced) tables correct?

they may be correct, but they don't make sense to me

why aren't they combined into a single table?

pearl2 · #5 (**permalink**) 03-31-09, 14:51

Thanks for asking that, r937.

I find myself wrestling with that question - whether to split the data into different tables or lump everything in one.

Here I'm splitting because the "users" table is used to contain essential information about the users. The "points" table stores all information related to points - there could be more than one type of points, e.g.

Code:

CREATE TABLE points (
  member_id MEDIUMINT(8) UNSIGNED NOT NULL,
  experience_points MEDIUMINT(8) UNSIGNED NOT NULL,
  merit_points MEDIUMINT(8) UNSIGNED NOT NULL,
  conduct_points MEDIUMINT(8) UNSIGNED NOT NULL,
  INDEX (member_id),
  CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Is that a good reason for splitting the data into different tables?

r937 · #6 (**permalink**) 03-31-09, 15:13

Quote:

Originally Posted by pearl2

Is that a good reason for splitting the data into different tables?

actually, no, now it's even worse

please note that over-simplifying your actual scenario leads to wasted time for everybody

your first post made sense only if the relationship were intended to eliminate the possibility of a NULL in the points column in a combined table (i.e. a user without any points)

now we see several different points, declared NOT NULL

if it made sense to split one possibly NULL attribute off into a one-to-one relationship, it makes far less sense now, where presumably different types of points are all declared as NOT NULL and forced to co-habit in the same row of a one-to-one table

put all these columns into the main table and let them be NULL

pearl2 · #7 (**permalink**) 03-31-09, 21:12

Thanks but I'm confused now

I've the following tables. Should I be putting all the columns in "preferences" and "quota" into the main table "users"?

Code:

CREATE TABLE users (
  id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(20) NOT NULL DEFAULT '',
  email VARCHAR(80) NOT NULL DEFAULT '',
  password VARCHAR(32) NOT NULL DEFAULT '',
  logged DATETIME,
  logins MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
  online ENUM('Y','N') NOT NULL DEFAULT 'Y',
  ip VARCHAR(50),
  UNIQUE KEY username (username),
  UNIQUE email (email)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE preferences (
  member_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
  enable_pm CHAR(1) DEFAULT '1',
  enable_chat CHAR(1) DEFAULT '0',
  enable_newsletters CHAR(1) DEFAULT '1',
  CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE quota (
  member_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
  job_quota TINYINT(2) UNSIGNED NOT NULL DEFAULT '20',
  vote_quota TINYINT(2) UNSIGNED NOT NULL DEFAULT '5',
  reset DATETIME,
  CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

r937 · #8 (**permalink**) 03-31-09, 21:37

Quote:

Originally Posted by pearl2

Should I be putting all the columns in "preferences" and "quota" into the main table "users"?

that's twice the tables have changed on me, you gonna change them again?

yes, i don't see why you wouldn't have those columns in the users table

why did you think you needed them separate?

pearl2 · #9 (**permalink**) 03-31-09, 21:58

My apologies on the tables...Looks like I have different problem now than the INDEX question I was asking

I split the data because I thought it I should put similar things together, so that in the future, I like needed to add a quota-related column, I just add it to the "quota" table, leaving the "users" table intact.

Is that a good approach?

If not, am I right to say we only have one table (B) related to another table (the main table A) if it's possible that B may not contain any data at all?

r937 · #10 (**permalink**) 03-31-09, 22:08

Quote:

Originally Posted by pearl2

I split the data because I thought it I should put similar things together

that's a good idea -- put all the user stuff together in one table

there are few reasons why you'd have a one-to-zero-or-one relationship, and i don't see a reason here

pearl2 · #11 (**permalink**) 03-31-09, 23:37

Many thanks for your patience, r397

I think I got it now.

So I should put all the columns in "quota" and "preferences" in the main table "users" since there's going to be only one row of each of the columns in "quota" and "preferences" in the "users" table.

Am I right?

Internet Services

Web Development

Digital Marketing

Consumer Tech