So i have the following table townResources in wich i store every resource value for every town ID. I am a bit reserved about performance impact for a large amount of users. I am thinking for moving the balance for resources to the towns table, and the general value of an resource to store it in a .php file :).

Here you have the townreources table:

CREATE TABLE IF NOT EXISTS `townresources` (
  `townResourcesId` int(10) NOT NULL AUTO_INCREMENT,
  `userId` int(10) NOT NULL,
  `resourceId` int(10) NOT NULL,
  `townId` int(10) NOT NULL,
  `balance` decimal(8,2) NOT NULL,
  `resourceRate` decimal(6,2) NOT NULL,
  `lastUpdate` datetime NOT NULL,
  PRIMARY KEY (`resourceId`,`townId`,`townResourcesId`,`userId`),
  KEY `townResources_userId_users_userId` (`userId`),
  KEY `townResources_townId_towns_townId` (`townId`),
  KEY `townResourcesId` (`townResourcesId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Stores Town Resources' AUTO_INCREMENT=9 ;

Wich is the best option in my case?:)

I'm working on a browser-based RTS, and we are targeting 10k+ users with multiple towns per user.

I have the following table townresources in which I store every resource value for every town ID:

CREATE TABLE IF NOT EXISTS `townresources` (
  `townResourcesId` int(10) NOT NULL AUTO_INCREMENT,
  `userId` int(10) NOT NULL,
  `resourceId` int(10) NOT NULL,
  `townId` int(10) NOT NULL,
  `balance` decimal(8,2) NOT NULL,
  `resourceRate` decimal(6,2) NOT NULL,
  `lastUpdate` datetime NOT NULL,
  PRIMARY KEY (`resourceId`,`townId`,`townResourcesId`,`userId`),
  KEY `townResources_userId_users_userId` (`userId`),
  KEY `townResources_townId_towns_townId` (`townId`),
  KEY `townResourcesId` (`townResourcesId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Stores Town Resources' AUTO_INCREMENT=9;

I'm a bit reserved about performance impact for a large amount of users. I'm thinking about moving the balance for resources to the towns table, and the general value of a resource to a PHP file. Is this a better option?