Redundant key definition in term_hierarchy & term_node tables? [#58942]

I noticed this when browsing the table definitions with phpMyAdmin. It showed the following warning:

PRIMARY and INDEX keys should not both be set for column `tid`

Here's the table definitions:

CREATE TABLE term_hierarchy (
  tid int(10) unsigned NOT NULL default '0',
  parent int(10) unsigned NOT NULL default '0',
  KEY tid (tid),                           <------- Redundant?
  KEY parent (parent),
  PRIMARY KEY (tid, parent)
);

CREATE TABLE term_node (
  nid int(10) unsigned NOT NULL default '0',
  tid int(10) unsigned NOT NULL default '0',
  KEY nid (nid),
  KEY tid (tid),                           <------- Redundant?
  PRIMARY KEY (tid,nid)
);

The same occurs in PostgreSQL:

CREATE TABLE term_hierarchy (
  tid integer NOT NULL default '0',
  parent integer NOT NULL default '0',
  PRIMARY KEY (tid, parent)
);
CREATE INDEX term_hierarchy_tid_idx ON term_hierarchy(tid);   <--- Redundant?
CREATE INDEX term_hierarchy_parent_idx ON term_hierarchy(parent);

CREATE TABLE term_node (
  nid integer NOT NULL default '0',
  tid integer NOT NULL default '0',
  PRIMARY KEY (tid,nid)
);
CREATE INDEX term_node_nid_idx ON term_node(nid);
CREATE INDEX term_node_tid_idx ON term_node(tid);   <------- Redundant?

I think phpMyAdmin means is there is no need to define an additional index because tid is the first item in the primary key.

Comments

Comment #1

Bairnsfather commented 11 September 2006 at 04:41

Version:

x.y.z

» 4.7.3

Any answers?

I had the same error messages. At first I thought my database was corrupt. So I loaded my old databases one after one and found they all had the same problem. Then I thought I'd start at the beginning and load the schema in a new install, having never logged in, and THAT gave me the same error.

I mean it seems crazy that the database schema ships with a major problem, so I'm hoping someone will explain to me why "check table" chokes on a clean install without having ever logged in. I looked around at the phpMyAdmin and MySQL sites and was not enlightened. I know I have a lot to learn about MySQL so I'm hoping someone can help me out with an explanation.

Here's my attempt at explaining the problem...longer because I know less. :-(
http://drupal.org/node/82101

Please help, inquiring minds want to know. :-)
-Drew

Comment #2

markus_petrux commented 11 September 2006 at 14:32

MySQL uses just one index to access the data. I guess any query in need to access by tid will use the primary key.

To know the truth it would require an EXPLAIN against the particular queries.

Cheers

Comment #3

markus_petrux commented 12 September 2006 at 23:38

You guys might want to look at this:
http://www.mysqlperformanceblog.com/2006/08/17/duplicate-indexes-and-red...

Cheers

Comment #4

gtcaz commented 28 September 2006 at 21:25

Duplicate to http://drupal.org/node/65456, which has been changed to a support request for some reason.

Comment #5

gtcaz commented 28 September 2006 at 21:53

Here's what I did to get rid of all the warnings but one.

ALTER TABLE `node` DROP INDEX `nid`;
ALTER TABLE `term_hierarchy` DROP INDEX `tid`;
ALTER TABLE `term_node` DROP INDEX `nid`;
ALTER TABLE `term_node` DROP INDEX `tid`;
ALTER TABLE `view_view` DROP INDEX `name_2`;

The remaining warning is that there's more than one INDEX key to column status in 'node'. This seems by design, so I left it alone.

From the CVS version of Drupal:

KEY status (status),
KEY node_promote_status (promote, status),
KEY node_status_type (status, type, nid),

Comment #6

gtcaz commented 28 September 2006 at 22:06

Please disregard the last part of my last comment -- for some reason I was reading the keys as new INDEXes. I'm deleting them as well. They're not in the structure of the current sql:

http://cvs.drupal.org/viewcvs/drupal/drupal/database/database.4.1.mysql?...