Indexes in node_revision [#46630]

I found that a lot of the node_load() requests were taking a long time to complete for the node_revisions table. I checked this out by doing an EXPLAIN of the query and found that the join to the node_revisions table was not using an index.

It seems that the primary key for node_revisons is both nid and vid, but the majority of the queries only use n.vid = r.vid as vid like nid is a global sequentical field.

I am not sure if we need to change the primary index or add an additional index to the node_revisions file, but this does need to be fixed.

For myself I have added a new index, which has taken the main node_load() from 17ms to 1.6ms

Comment	File	Size	Author
#8	schema_0.patch	2.48 KB	Cvbge
#8
#6	schema.patch	1.33 KB	gordon
#6

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

Bèr Kessels CreditAttribution: Bèr Kessels commented 2 February 2006 at 08:53

Can you post a patch? or a SQL command so that we can test?
Devel.module confirms the extremely long queries here.

Comment #2

killes@www.drop.org CreditAttribution: killes@www.drop.org commented 2 February 2006 at 09:51

I've another report from Aaron Welch that an index is needed. Please provide a patch.

Comment #3

killes@www.drop.org CreditAttribution: killes@www.drop.org commented 2 February 2006 at 10:08

Priority:

Critical

» Normal

not critical

Comment #4

Zen CreditAttribution: Zen commented 2 February 2006 at 10:15

Related issue: 35548.

Clean 4.7 installs have a primary index of nid + vid.
Upgraded installations have nid and vid as separate indices.

-K

Comment #5

killes@www.drop.org CreditAttribution: killes@www.drop.org commented 2 February 2006 at 10:20

That would explain why I didn't see the problem. :p

Indeed:

PRIMARY KEY (`vid`),
KEY `nid` (`nid`),
KEY `uid` (`uid`)

If we agree that this is the better structure, then we should simply change the database schemas.

Comment #6

gordon CreditAttribution: gordon commented 3 February 2006 at 00:12

Status:

Active

» Needs review

File	Size
schema.patch	1.33 KB

I have investigated this further, and found that in update_146 the indexes were changed, but the schema was not.

Here is a patch which should fix the initial load. I have also changed the pgsql as well but I can't test it. I think it is right.

Comment #7

Zen CreditAttribution: Zen commented 3 February 2006 at 07:33

Status:

Needs review

» Needs work

@Gordon: pgSQL - nid shouldn't be a sequence in the node_revisions table. It should just be an index.

Looks good otherwise :)

Thanks,
-K

Comment #8

Cvbge CreditAttribution: Cvbge commented 3 February 2006 at 10:08

Status:

Needs work

» Needs review

File	Size
schema_0.patch	2.48 KB

Fixed missing "," in database.mysql, removed unneeded sequence in database.pgsql and modified updates.inc to reflect the changes.

Not tested, but look quite good.

Comment #9

gordon CreditAttribution: gordon commented 23 February 2006 at 23:37

I am reluctant to up this to critical, but this needs to be commited before RC, otherwise we will have a number of freshly installed Drupal Sites that will be running poorly because there is a missing index.

As you can see from my really basic tests above, without the index there is a significant proformance hit.