SELECT / UPDATE and SELECT / INSERT kill the MySQL server

Question

I run a server that gets killed by the number of requests it receives from one of our iPhone games to store points. By this I mean that the server becomes unresponsive.

I only know enough MySQL / PHP to get through, so I'm floundering in my attempts to fix this. I am sure that this is a problem that can be optimized because we have a dedicated server. We only process 300 requests per minute.

We basically check the account that someone sends out of their game on the iPhone (using SELECT) to find out if they have an existing account. If they do, and their new score is better, we do an UPDATE, otherwise we do INSERT. Statements are as follows:

$ sql = "SELECT id, score, level FROM $ table WHERE board = '$ board' AND name = '$ name' AND udid = '$ udid' AND fbuid = '$ fbuid'"

The UPDATE and INSERT statements are as follows:

$ sql = "UPDATE $ table SET score = '$ score', level = '$ level', country = '$ country', date = CURRENT_TIMESTAMP WHERE board = '$ board' AND name = '$ name' AND udid = ' $ udid 'And fbuid =' $ fbuid '"

$ sql = "INSERT INTO $ table (board, udid, fbuid, name, score, level, country) VALUES ('$ board', '$ udid', '$ fbuid', '$ name', '$ rating', '$ level', $ country ') "

And for completeness, here is the definition of the table:

$table (
  id int (11) NOT NULL auto_increment,
  board tinyint (4) NOT NULL,
  udid varchar (45) default NULL,
  fbuid bigint (20) unsigned default NULL,
  name varchar (25) default NULL,
  country tinyint (4) default NULL,
  level tinyint (4) default NULL,
  score (10,0) NULL,
  date timestamp NOT NULL default CURRENT_TIMESTAMP,
   (id),
   scoreidx (score),
   udididx (udid),
   fbuididx (fbuid),
   boardidx (board),
   levelidx (level),
   countryidx (country)
  ) ENGINE = MyISAM DEFAULT CHARSET = latin1

MySQL PHP:

$conn = mysql_pconnect (DB_HOST, DB_USER, DB_PASSWORD, MYSQL_CLIENT_INTERACTIVE);

msql_connect, .

, , -, , , .

Answer 1

MyISAM InnoDB. , InnoDB ( ) mysqlslap -.

, :

ALTER TABLE tablename ADD INDEX(board,name,udid,fbuid)

MySQL , "" , .

Answer 2

"MySQL MyISAM, MEMORY MERGE, BDB InnoDB."

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

, , , . , InnoDB.

Answer 3

, . , - .

$sql = "INSERT INTO $table(board, udid, fbuid, name, score, level, country) VALUES
('$board', '$udid', '$fbuid', '$name', '$score', '$level', $country') ON DUPLICATE KEY
UPDATE score = '$score'";