MySQL5 supports compound Primary keys, and a "smart" auto_incrementer. I don't know if Postgres or other DB engines do, but this could offer a significant improvement in the database lifecycle and storage capacity of a Drupal site. It may even improve indexing.
To understand what I'm referring to, look at the "animals" demo code in: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
Basically, what I see in Drupal 6 and 7 in the node_revision table is this: the Revision ID is the primary key and auto_increments on its own. That's acceptable, but the key design could be improved. Because MySQL offers compound primary keys, we could set it up like such: (i'm leaving out additional fields for the sake of this example).
create table node_revision (
nid int unsigned not null,
vid int unsigned auto_increment not null,
uid int unsigned not null,
title char(255) not null default 'Untitled',
PRIMARY KEY (nid, vid)
);
insert into node_revision (nid,uid,title) values
(1, 1, 'My Test node'),
(1, 1, 'I renamed my Test node'),
(1, 1, 'I fixed the name of my test node'),
(2, 1, 'This is a different node'),
(2, 1, 'I renamed my different node'),
(2, 1, 'I renamed the different node again'),
(3, 1, 'Pretty, cool, huh?');
select * from node_revision;
See the vid field? It only increases when an NID&VID are already present. (And yes, I did show title as "char" instead of "varchar" because its easier for mysql to index and read than a variable width field.)
In this manner, every time a revision is created, it's not indexing from the highest revision number already in the database, its starting from the highest revision number known on that node. The advantage is that our row limit in every table with revisioning increases from 2.1bn records to (2.1*2.1)bn records.
Nodes aside, I feel this could be applied to any table that handles revisioning, and would really like to see it implemented into Drupal 8. Ideally, modules that manage revisions of their own content could do this in this manner as well.
| Comment | File | Size | Author |
|---|---|---|---|
| #4 | improve-node-revision-indexes-1121402.entityapi.patch | 740 bytes | wjaspers |
| #4 | improve-node-revision-indexes-1121402.node_.patch | 5.44 KB | wjaspers |
| #2 | improve-node-revision-indexes-1121402.entityapi.patch | 752 bytes | wjaspers |
| #2 | improve-node-revision-indexes-1121402.node_.patch | 5.56 KB | wjaspers |
Comments
Comment #1
wjaspers commentedTested this in 7.x-dev.
16-may-2011
Strange thing -- I still can't get the title to pull up correctly. For some reason, the highest matching VID to the node NID is always the title used, but the content pulled from the field_api is correct. Not to mention all the node links generated are accurate (node/2/revision/3/view, node/5/revision/3/view).
17-may-2011:
SOLVED! I dont know how dangerous it is to modify the Entity query conditions for revisioning, but if we enforce the compound key to check both the base table entity ID and the identified revision ID, it all works out.
Changed line 260 in includes/entity.inc:
FROM:
$query->join($this->revisionTable, 'revision', "revision.{$this->revisionKey} = base.{$this->revisionKey}");TO:
$query->join($this->revisionTable, 'revision', "revision.{$this->revisionKey} = base.{$this->revisionKey} AND revision.{$this->idKey} = base.{$this->idKey}");Figured it all out; but I'm willing to bet the -dev code on Node I've been using is a bit out of date. Code to follow shortly.
Comment #2
wjaspers commentedThe only thing that doesn't seem to work properly here is that ubercart product nodes can lose track of each other because the product table indexes won't coincide with the new design--but a minor fix can resolve that.
Heres the patches, as previously mentioned:
Comment #3
wjaspers commentedComment #4
wjaspers commentedConfounded WinMerge generated Win32 style line endings---AGAIN.
Lets try this once more.
Comment #18
smustgrave commentedWonder if this is still the case for D10?
Comment #20
smustgrave commentedFollowing up if still a desired feature, if no follow up in 3 months will close out.
Comment #21
smustgrave commentedSince there's been no follow up and a feature request going to close out. Can always be re-opened
Thanks!