[SOLVED] two table updates

[Crusader]

Hi again. I've got these two updates working properly but I'm not too sure if I've done them properly or elegantly. Running MySQL 4.1.

 

Query 1:

UPDATE LOW_PRIORITY `player_cargo` c, `player_ships` s 
SET c.amount = c.`amount` + ?, s.`open_holds` = s.`open_holds` - ? 
WHERE c.`id` = ? AND (s.`pilot` = ? AND s.`game` = ?)

 

Query 2:

PDATE LOW_PRIORITY `player_ships` s, `players` p 
SET s.`sector` = ?, s.`galaxy` = ?, p.`turns` = p.`turns` - ? 
WHERE (s.`game` = p.`game` 
AND s.`pilot` = p.`player_id`) 
AND p.`user_id` = ?

 

Table structures:

CREATE TABLE `players` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `game` tinyint(3) unsigned NOT NULL default '0',
  `player_id` smallint(5) unsigned NOT NULL default '0',
  `user_id` smallint(5) unsigned NOT NULL default '0',
  `name` varchar(32) NOT NULL default '',
  `race` tinyint(2) unsigned NOT NULL default '0',
  `turns` float(4,1) unsigned NOT NULL default '100.0',
  `credits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game` (`game`),
  KEY `player_id` (`player_id`),
  KEY `user_id` (`user_id`),
  KEY `name` (`name`),
  KEY `race` (`race`),
  KEY `turns` (`turns`),
  KEY `credits` (`credits`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `player_cargo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `player_id` smallint(5) unsigned NOT NULL default '0',
  `game` tinyint(3) unsigned NOT NULL default '0',
  `good` tinyint(3) unsigned NOT NULL default '0',
  `amount` smallint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `player_id` (`player_id`),
  KEY `game` (`game`),
  KEY `good` (`good`),
  KEY `cargo` (`player_id`,`game`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `player_ships` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `game` tinyint(3) unsigned NOT NULL default '0',
  `pilot` smallint(5) unsigned NOT NULL default '0',
  `type` tinyint(3) unsigned NOT NULL default '1',
  `galaxy` tinyint(2) unsigned NOT NULL default '0',
  `sector` smallint(5) unsigned NOT NULL default '1',
  `armour` smallint(5) unsigned NOT NULL default '50',
  `hull` smallint(5) unsigned NOT NULL default '50',
  `holds` smallint(4) unsigned NOT NULL default '5',
  `open_holds` smallint(4) unsigned NOT NULL default '0',
  `weapons` varchar(32) NOT NULL default '0',
  `mines` smallint(4) unsigned NOT NULL default '0',
  `fighters` smallint(4) unsigned NOT NULL default '0',
  `scouts` smallint(4) unsigned NOT NULL default '0',
  `docked` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game` (`game`),
  KEY `pilot` (`pilot`),
  KEY `type` (`type`),
  KEY `galaxy` (`galaxy`),
  KEY `sector` (`sector`),
  KEY `docked` (`docked`),
  KEY `contact` (`pilot`,`sector`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

Also... am I using too many indexes?

 

Thanks!

[Crusader]

Note: Query 2 should say UPDATE.

[Crusader]

Solved it myself. I used INNER JOINS instead.