Solved

Slow update in InnoDB

Posted on 2006-06-26
7
408 Views
Last Modified: 2012-06-27
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
Comment
Question by:ditkis
7 Comments
 
LVL 19

Expert Comment

by:Kim Ryan
Comment Utility
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
 

Author Comment

by:ditkis
Comment Utility
Dual Xeon, 4G... I consider 3 updates per minute very slow for as they blame "enterprise level" DB.
0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 250 total points
Comment Utility
You need an index on (user_id, custom_id):

ALTER TABLE `values` ADD INDEX (`user_id`, `custom_id`);
0
 
LVL 22

Assisted Solution

by:NovaDenizen
NovaDenizen earned 250 total points
Comment Utility
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
 
LVL 22

Expert Comment

by:NovaDenizen
Comment Utility
Actually, you might want to add the new index before you drop the old one.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now