performance - A really slow UPDATE to a MySQL server -

i running cms, has nothing it.

i have simple query is:

update e107_online set `online_location` = 'http://page.com/something.php?', `online_pagecount` = 133 `online_ip` = '175.44.*.*' , `online_user_id` = '0' limit 1; 

but same query reported website support gives that:

user@host: cosyclim_website[cosyclim_website] @ localhost []   thread_id: 7493739 schema: cosyclim_website   query_time: 12.883518 lock_time: 0.000028 rows_sent: 0 rows_examined: 0 rows_affected: 1 rows_read: 1 

it takes 12 (almost 13) seconds simple update query? there way optimize somehow? if run through phpmyadmin takes 0.0003s.

the table:

create table if not exists `e107_online` (   `online_timestamp` int(10) unsigned not null default '0',   `online_flag` tinyint(3) unsigned not null default '0',   `online_user_id` varchar(100) not null default '',   `online_ip` varchar(15) not null default '',   `online_location` varchar(255) not null default '',   `online_pagecount` tinyint(3) unsigned not null default '0',   `online_active` int(10) unsigned not null default '0',   key `online_ip` (`online_ip`) ) engine=myisam default charset=latin1; 

your query updating 1 row meets criteria:

update e107_online     set `online_location` = 'http://page.com/something.php?', `online_pagecount` = 133     `online_ip` = '175.44.*.*' , `online_user_id` = '0'     limit 1; 

given have ip addresses, i'm guessing table pretty big. millions , millions , millions of rows. there many reasons why update can take long time -- such server load, blocking transactions, , log file performance. in case, let's make assumption problem finding 1 of rows. can test doing select same conditions , see how long takes.

assuming select consistently slow, problem can fixed indexes. if table has no indexes -- or if mysql cannot use existing indexes -- needs full table scan. and, perhaps 1 record matches @ end of table. takes while find it.

i suggest adding index on either e107_online(online_ip) or e107_online(online_user_id, online_ip) find record faster. index needs b-tree index, explained here.

one consequence of using index ip lowest matching value 1 chosen. don't know if lack of randomness makes difference in application.