CODEWITHSUNDEEP
mysqldatabase-performance
nrob
nrob

Reputation: 340

simple update query is slow

I have a MySQL UPDATE query, and I can't figure out, why is it so slow. The query is UPDATE profile SET lastfresh=? WHERE uid=?

The table:

CREATE TABLE `profile` (
  `uid` mediumint(6) NOT NULL AUTO_INCREMENT,
  `nick` varchar(16) NOT NULL DEFAULT '',
  `lastfresh` int(10) unsigned NOT NULL DEFAULT '0',
  ....
  PRIMARY KEY (`uid`),
  KEY `adtk_nck` (`nick`),
  KEY `adtk_wnl` (`nick`,`lastfresh`),
  KEY `adtk_np` (`nick`,`pass`),
  KEY `lng` (`lng`)
) ENGINE=InnoDB AUTO_INCREMENT=182993 DEFAULT CHARSET=utf8;

The duration of query's execution is 17 seconds (sometimes). What can be a problem?

Upvotes: 0

Views: 102

Answers (1)

Peeyush Kushwaha
Peeyush Kushwaha

Reputation: 3623

fine-tune your mysql settings.

One thing that is very important for performance of innodb engine is the innodb_buffer_pool_size setting. Its default value is 8 mb which is very small. Follow the suggestions in this article : http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

if you want to understand how buffer pool works, this is the link to mysql docs: http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

Upvotes: 1

Related Questions