best set of indices

Question

This is a MySQL case with 5,000,000 records in trade table, and 5,000,000 records in registries table.

little cases exist where uni is duplicate. i.e. values of uni is almost different. same comment for other columns.

  update trades t
  inner join registries r on
    ( t.uni = r.uni and r.nationalid <> '' )
    or ( t.account = r.account and r.nationalid = '' )
  set
    t.registry_id = r.rowid

this statement takes about 1 hour to complete.

indices:

create index idx1 on trades (uni);
create index idx2 on trades (account);
create index idx3 on trades (account);
create index idx1 on registries (nationalid, uni);
create index idx2 on registries (account);
create index idx3 on registries (uni);

what are any indices or configurations that I have to apply to get best performance?

Answer 1

Use two separated updates instead, to get rid of the OR operator.

First update

update trades t
inner join registries r on t.uni = r.uni and r.nationalid <> '' 
set t.registry_id = r.rowid

The following indexes would speed up the first update

create index un_regid on trades (uni , registry_id);
create index un_nat_rid on registries (uni , nationalid, rowid );

---

Second update

update trades t
inner join registries r on t.account = r.account and r.nationalid = '' 
set t.registry_id = r.rowid

The following indexes would speed up the second update

create index acc_regid on trades (account , registry_id);
create index un_nat_rid on registries (account , nationalid, rowid );