Let me post the DDL of the node table:

CREATE TABLE node (
  nid int(10) unsigned NOT NULL auto_increment,
  vid int(10) unsigned NOT NULL default '0',
  type varchar(32) NOT NULL default '',
  title varchar(128) NOT NULL default '',
  uid int(10) NOT NULL default '0',
  status int(4) NOT NULL default '1',
  created int(11) NOT NULL default '0',
  changed int(11) NOT NULL default '0',
  comment int(2) NOT NULL default '0',
  promote int(2) NOT NULL default '0',
  moderate int(2) NOT NULL default '0',
  sticky int(2) NOT NULL default '0',
  PRIMARY KEY (nid),
  KEY node_type (type(4)),
  KEY node_title_type (title,type(4)),
  KEY status (status),
  KEY uid (uid),
  KEY vid (vid),
  KEY node_moderate (moderate),
  KEY node_promote_status (promote, status),
  KEY node_created (created),
  KEY node_changed (changed),
  KEY node_status_type (status, type, nid)
)

Note that there are 2 indexes for status:
1) KEY status (status)
2) KEY node_status_type (status, type, nid)

...but only one is needed.

Comments

markus_petrux’s picture

Priority: Normal » Critical

Critical, in terms of performance?

killes@www.drop.org’s picture

Priority: Critical » Normal

probably not.

markus_petrux’s picture

ok, but queries are resolved by the engine dynamically. ie. the engine needs to evaluate the best "path" to access the data requested by the query. The more indexes defined, the more things the engine needs to check. Also, there is the waste of space for a useless index.

Not sure, in terms of milliseconds, but it might be significant on sites with a lot of nodes. Anyway... that's fine.

gerhard killesreiter’s picture

I am not saying that it isn't a bug if we really don't need the second index, but it isn't critical if a large site runs a little bit slower.

markus_petrux’s picture

Aside from fixing the DDL in the database directory...

Is it enough an update step to remove the index?

nigma3d’s picture

i am running a large drupal site and nodes are taking 5 - 10 seconds to load. i have tried everything and think that this *might* be the issue? any solutions?

markus_petrux’s picture

Well, a redundant index may probably affect updates more than reads.

There might be other causes for your problem. Have you tried the devel.module? It has an option that lists the executed queries and the time each one took. So you can then focus on trying to optimize that particular issue.

The problem may even come from a module...

nigma3d’s picture

my site is actually has a *ton* of node updates and why I am thinking this is the root of my problem. Each of my 10,000 active users post new nodes and update their nodes quite often. I have used the devel module extensively, and optimized everything.

pwolanin’s picture

Similar problems seem to exist in the taxonomy tables, though I haven't gotten around to posting an issue yet. PHPmyAdmin puts up big warning messages every times i'm browsing the taxonomy tables.

pwolanin’s picture

see also: http://drupal.org/node/76040

these seem to be duplicate- but clearly the issue is not resolved yet

magico’s picture

Status: Active » Closed (duplicate)