CODEWITHSUNDEEP
mysql
vbbartlett
vbbartlett

Reputation: 179

Mysql optimization: Update statement on large tables

I have two tables:

playerstatstable, entrytable (see below)

playerstatstable has 100,000 rows and entrytable has 1 mil rows

What I want to do is update playerstatstable as follows

UPDATE playerstatstable set totalScore = totalScore+1000 where PlayerID = (Select PlayerID from entrytable where score = 1 and entryState = 5 and playerstatstable.PlayerID = entrytable.PlayerID);

About 1/10 of the entries will be in state 5 and of that about 1/5 will have the score = 1;

So I have about 20,000 entries that are associated with 20,000 of the playerstatstable rows which need updated.

1) is there a better update statement?
2) is there any index/key arrangement that would improve this?

CREATE TABLE `playerstatstable` (   
  `PlayerID` int(11) NOT NULL,  
  `totalScore` int(11) DEFAULT '0', 
  PRIMARY KEY (`PlayerID`), 
  UNIQUE KEY `PlayerID_UNIQUE` (`PlayerID`),    
  KEY `idx_m` (`monthTime`),    
  KEY `idx_w` (`weekTime`), 
  KEY `idx_d` (`dayTime`)   
) ENGINE=InnoDB DEFAULT CHARSET=utf8;   


CREATE TABLE `entrytable` (
  `EntryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PlayerID` int(10) unsigned DEFAULT '0',
  `entryType` bit(1) DEFAULT b'0',
  `entryState` int(11) DEFAULT '1',
  `score` int(11) DEFAULT '0',
  `rank` int(11) DEFAULT '0',
  PRIMARY KEY (`EntryID`),
  UNIQUE KEY `EntryID_UNIQUE` (`EntryID`)
) ENGINE=InnoDB

Upvotes: 0

Views: 357

Answers (1)

RGV
RGV

Reputation: 742

Please try this. 

UPDATE t1 set t1.totalScore = t1.totalScore+1000 
from playerstatstable t1  inner join entrytable t2
on t.PlayerID = t2.PlayerID 
and t2.score = 1 and t2.entryState = 5

For MYSQL

UPDATE
playerstatstable t1, entrytable t2
set t1.totalScore = t1.totalScore+1000 
where t.PlayerID = t2.PlayerID 
and t2.score = 1 and t2.entryState = 5

Upvotes: 1

Related Questions