vB index upgrade

[tamarian]

Hi folks,

I've upgrading since 1.1.3 or 4, till now 2.0.1, and looking at the diff between my current schema and a clean vB schema, there a lot of differences on table indexes, I think it's because upgrade scripts can't, won't or shouldn't change them? Or might be remnant of the old hacks, some of which became standard.

Some tables have and index like this in the new vB:
UNIQUE wordid (wordid, postid)

while on mine they're:
PRIMARY KEY (wordid, postid)

And lots similar cases.

It's working fine, but would it be more uptimized to change to match the new vB2 schema?

Any danger of corruption or loss of data? I tried on my test system to drop the the keys and alter the table to have the keys match the new schema. I did a couple and nothing seemed broken, but would like to hear from someone who knows before I do any such thing on my live forum :)

[Mike Sullivan]

PRIMARY KEY (wordid,postid) = UNIQUE PRIMARY (wordid,postid)

So don't worry about that.

[tamarian]

Thanks Ed, this will make it a lot easier.

Another diff in the privatemessage table where mine is indexed by fromuserid while a clean vb is indexed by userid. In this case I should drop the fromuseid key and add key for userid?

And finally, there are some size differences a smallint(6) became tinyint(4) , a varchar[64] became varchar[255]. Should the sizes be changed to match the new 2.0 schema or leave alone?

Sorry for sounding very knowledgable about MySQL ;)

TIA

[Mike Sullivan]

Your forum is really to small to worry about the privatemessage stuff -- you could really survive without any indexes. But it does get to be an issue with 30,000 privatemessages...

But I would recommend whatever the schema from install.php is.

smallint vs tinyint isn't a big deal unless it's a field that needs all ~65000 entries smallint allows.

varchar(64) is limited to 64 characters. varchar(255) is 255. If 64 isn't an issue for YOU, then it's not a problem.