Slow update in InnoDB


Here is my table:
CREATE TABLE `values` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `client_id` int(11) NOT NULL default '0',
  `custom_id` int(11) NOT NULL default '0',
  `value_text` varchar(255) NOT NULL default '',
  `value_digit` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`,`client_id`),
  KEY `custom_id` (`custom_id`)

That table has 70+ million records and it is 7.2G.

Here is the slow update:
UPDATE values SET user_id=$USERID, custom_id=$CUSTOMID
AND custom_id=$CUSTOMID

Only 3 of those update a second can be done... I'm using MySQL 5.0. Does anybody know what's wrong with it?

Who is Participating?
todd_farmerConnect With a Mentor Commented:
You need an index on (user_id, custom_id):

ALTER TABLE `values` ADD INDEX (`user_id`, `custom_id`);
Kim RyanIT ConsultantCommented:
Speed is all relative, how many updates per second would you consider not slow? As each update statement has to scan up to 70 million rows, I would have thought 3 per second is fast.

You could get possibly get minor improvements by tuning some database parameters, but as updates involves rewriting data this could be very limited. What type of hardware are you using?
ditkisAuthor Commented:
Dual Xeon, 4G... I consider 3 updates per minute very slow for as they blame "enterprise level" DB.
NovaDenizenConnect With a Mentor Commented:
ALTER TABLE values DROP INDEX custom_id;
ALTER TABLE values ADD INDEX (custom_id, client_id);

With only the (custom_id) index present, mysql has to scan through all records with the correct custom_id to find ones with the matching client_id.  With the (custom_id, client_id) index, mysql will go directly to the records it has to change, without having to scan through any extranneous records.

Actually, you might want to add the new index before you drop the old one.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.