?
Solved

Slow update in InnoDB

Posted on 2006-06-26
7
Medium Priority
?
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 16990094
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
ID: 16992249
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 1000 total points
ID: 16993169
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 1000 total points
ID: 16993185
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
ID: 16993195
Actually, you might want to add the new index before you drop the old one.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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