Jump to content

[Patch] [8332] Create index on realmcharacters.acctd

Guest rilex

Recommended Posts

What features does the patch add?

An index on acctid in the realmcharacters table on the realmd database

For which repository revision was the patch created?

I don't think it matters, but the 0.14 branch.

Who has been writing this patch?


create index idx_acctid ON realmcharacters (acctid)

This will put an index on acctid, so when doing joins between account.id and realmcharacters.id on a database with a large number of accounts (>8K tested), the performance isn't abysmal.

Link to comment
Share on other sites

leak, do you have any idea why the index wouldn't be used? It is used in a JOIN/RIGHT JOIN.

EDIT: Oh, I get it. 'acctid' cannot be a PK. It is marked as such, but it can (and in this particular database, does) contain duplicate ids. So this should probably be marked as a bug.

Link to comment
Share on other sites

  • 4 weeks later...
I get it. 'acctid' cannot be a PK. It is marked as such, but it can
Agree, with index need but not agrre with strange remark: primary key is _pair_ of fields.


Not mangos bug. Possible some old DB porjects versions have wrong different from mangos realmd.sql keys for table and not add propertly expected primary key.

So adding any mangos sql update is useless (mangos _has_ proper key). And must be fixed by users.

Just for note: i also found in my long existed DB this table without key.

Link to comment
Share on other sites

I have the key, in Sqylog as:

Indexes: PRIMARY

Columns: realmid,accid

Unique: Checked

Also, here is the create script from the current realmd.sql:


-- Table structure for table `realmcharacters`


DROP TABLE IF EXISTS `realmcharacters`;

CREATE TABLE `realmcharacters` (

`realmid` int(11) unsigned NOT NULL default '0',

`acctid` bigint(20) unsigned NOT NULL,

`numchars` tinyint(3) unsigned NOT NULL default '0',

PRIMARY KEY (`realmid`,`acctid`)


However, doing this join will still lead to a table scan and not using an index, hence the need for an index.

Link to comment
Share on other sites

FWIW, if you do:

SELECT `account`.*, `realmcharacters`.* FROM `account`

LEFT OUTER JOIN `realmcharacters` ON `account`.ID = `realmcharacters`.acctid

WHERE `realmcharacters`.acctid IS NOT NULL

That will force MySQL to use the secondary PK on realmcharacters (IOW, the acctid PK instead of realmid PK). Apparently MySQL will use PKs in the order specified, so acctid will never be used if realmid ISN'T used in your query. This is probably why I discovered that specifically creating an account on acctid resolved my perf issue (but the above query works around that).

Link to comment
Share on other sites

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Privacy Policy Terms of Use