I've got a table with 9,451,483 records in it. I'm running a delete
which should get rid of about 480,000 of those rows. The delete has
gone one for over 10 minutes, while all other inserts into that table
have blocked. This quickly filled up my connection table as the web
site posting the data is quite busy...
Anyhow, what can I do to make the delete either 1) go faster (a select
on the same data takes only about 2 minutes) or 2) not block the
inserts?
The table is structured like this (non-relevent and non-key fields
deleted):
user_id int(10) unsigned DEFAULT '0' NOT NULL,
game_name varchar(6) DEFAULT '' NOT NULL,
game_serial mediumint(8) unsigned DEFAULT '0' NOT NULL,
play_validation varchar(44) DEFAULT '' NOT NULL,
play_id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
play_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (play_id),
KEY game_name (game_name,game_serial),
KEY play_time (play_time),
UNIQUE play_validation (play_validation),
KEY user_id (user_id)
The delete in question looks like this:
DELETE FROM mytable WHERE game_name='abcdef' AND game_serial=5
I'm running 3.22.14-gamma, for sun-solaris2.6 on sparc (binary
distribution) as installed by the hosting company on our dedicated
box.
Unfortunately, the server monitor program noticed that mysql was full
of connections and killed the server before I could kill the thread,
so I think I lost some data. Bummer. The table index is corrupted as
well, so isamchk is fixing it now...
Any pointers to getting this delete to finish reasonably fast would
help greatly. Thanks!
