Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Slow update in InnoDB

Posted on 2006-06-26
7
Medium Priority
?
426 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

610 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