Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Slow update in InnoDB

Hello,

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=164369727 ;

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
WHERE client_id=$CLIENTID
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?

Thanks
0
ditkis
Asked:
ditkis
2 Solutions
 
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?
0
 
ditkisAuthor Commented:
Dual Xeon, 4G... I consider 3 updates per minute very slow for as they blame "enterprise level" DB.
0
 
todd_farmerCommented:
You need an index on (user_id, custom_id):

ALTER TABLE `values` ADD INDEX (`user_id`, `custom_id`);
0
 
NovaDenizenCommented:
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.

0
 
NovaDenizenCommented:
Actually, you might want to add the new index before you drop the old one.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now