I just checked the design of some core tables and to my surprise I think that there are a lot of redundant indexes on core drupal tables.
Removing those indexes could result in a huge performance benefit while inserting or updating (not to mention a reduced database size).

Let us examine for example the table term_node:

CREATE TABLE `term_node` (
  `nid` int(10) unsigned NOT NULL default '0',
  `tid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`tid`,`nid`),
  KEY `nid` (`nid`),
  KEY `tid` (`tid`)
)

There are three indexes on this table:
primary key, columns: tid, nid (important is the ordering of the columns in the primary key)
index nid, column: nid
index tid, column: tid

The index tid is a non necessary index. Queries that would never use it since the first column on the primary key is the same.

Also on the node!!! table we same:
primary key, columns: ,nid,vid
index nid, column: nid

Testcase on term_node table:

  • remove the index tid
  • explain extended select *
from   term_node
where  tid = 6;

As far as I can see, the query is using the primary key index.

I used to be a Oracle developer and the above is definately true for Oracle database. I am not for 100% sure if it is also true for Mysql but the explain query shows that they are redundant.

Please let me know if I am wrong here.

-----------------------------------------
Joep
CompuBase, Drupal websites and design

Comments

kbahey’s picture

kbahey commented
Priority: Critical » Normal

The other side of the argument is this article: Redundant index is not always bad.

Of course, depends on data set size and cardinality, so hard to come with a blanket rule.

Can you (or anyone else) run benchmarks with/without the redundant indexes and see if there is a noticable difference?

Anonymous’s picture

Anonymous (not verified) commented
Version: 5.2 » 6.x-dev
Priority: Normal » Critical

I changed the version to 6.x-dev.
However the origal issue was based on D5.

The same issue is still on D6.

For example the table term_node:

CREATE TABLE `term_node` (
  `nid` int(10) unsigned NOT NULL default '0',
  `vid` int(10) unsigned NOT NULL default '0',
  `tid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`vid`,`tid`,`nid`),
  KEY `nid` (`nid`),
  KEY `tid` (`tid`),
  KEY `vid` (`vid`)
)

The index vid is redundant since it is the first column of the primary key.

Anonymous’s picture

Anonymous (not verified) commented

@kbahey

The article you mentioned shows exactly where the pain is:

The drawback is worse INSERT performance, here is time to INSERT 1million records:
only with state_id KEY - 72 sec
with state_id and state_id_2 KEYs - 470sec
.......

I assume that the default design of a table is to not use redundant indexes.
There could be situation where you could benefit where the index has all the columns you are looking for.
But that is not the case here.

I have not checked al tables but I assume that the standard Drupal table design is to create an index for every primary key column.

Cheers, Joep

catch’s picture

catch
he/him
Primary language English
commented
Status: Active » Closed (duplicate)

This should be merged with:
http://drupal.org/node/164532

Anonymous’s picture

Anonymous (not verified) commented

I agree that this should be merged. Thanks for pointing to the other issue.
I am not sure whethe the status should be set to duplicate since the issues are related but are not discussing the same problem.

peterx’s picture

peterx commented

Perhaps we could set up a common page describing the reasons for indexing certain ways and reference the page in both issues. A person can then find either issue from a search and reach the description page.

catch’s picture

catch
he/him
Primary language English
commented
Priority: Critical » Normal
Status: Closed (duplicate) » Active

http://drupal.org/files/issues/schemas.patch

This patch I believe removes the redundant index, going to re-roll it for http://drupal.org/node/164532 so these two will be properly merged. Setting this back to active in case that doesn't get in - so this can be dealt with in it's own right later.

Anonymous’s picture

Anonymous (not verified) commented

Thanks, but I think the patch is not complete. For, example the node table is not listed.

dpearcefl’s picture

dpearcefl commented
Status: Active » Postponed (maintainer needs more info)

Is this still a problem in current D6?

dpearcefl’s picture

dpearcefl commented
Status: Postponed (maintainer needs more info) » Active
catch’s picture

catch
he/him
Primary language English
commented
Status: Active » Closed (won't fix)

Indexes were overhauled in D7.