Closed (duplicate)
Project:
Drupal core
Version:
x.y.z
Component:
database system
Priority:
Normal
Category:
Bug report
Assigned:
Unassigned
Reporter:
Created:
22 Apr 2006 at 07:36 UTC
Updated:
21 Sep 2006 at 05:47 UTC
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
Comment #1
markus_petrux commentedCritical, in terms of performance?
Comment #2
killes@www.drop.org commentedprobably not.
Comment #3
markus_petrux commentedok, 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.
Comment #4
gerhard killesreiter commentedI 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.
Comment #5
markus_petrux commentedAside from fixing the DDL in the database directory...
Is it enough an update step to remove the index?
Comment #6
nigma3d commentedi 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?
Comment #7
markus_petrux commentedWell, 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...
Comment #8
nigma3d commentedmy 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.
Comment #9
pwolanin commentedSimilar 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.
Comment #10
pwolanin commentedsee also: http://drupal.org/node/76040
these seem to be duplicate- but clearly the issue is not resolved yet
Comment #11
magico commented