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

[rilex]

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?

Rilex

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.

[leak]

acctid is already indexed because it is part of the primary key.

[rilex]

If you do a LEFT JOIN between acctid and account.id, it doesn't use an index. Putting an explicit index on acctid resolves the issue.

[leak]

Yep, confirmed.

[rilex]

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.

[rilex]

Bumping up.

[VladimirMangos]

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.

[added]

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.

[rilex]

As long as the index is created, I'm happy

[rilex]

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`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Realm Character Tracker';

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

[rilex]

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).

[VladimirMangos]

I add key in [8332] abd related backport to 0.12.

Thank you! for patch and description problem source

As i note alsready: absent primary key for affected table still be user problem: mangos have it.

[rilex]

Thank you!

[apendix]

i have solved my problem, sry for post

[rilex]

A) You should create your own post for this, but

B) You shouldn't post about this. MaNGOS only supports up to GM Level 4 (which is the console itself).