mysql slow on updates for a well optimized query

Question

i am facing an issue with update state , which takes up to 4s on very small queries .

Count: 1  Time=3.87s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), ***[***]@localhost
  UPDATE p_villages v SET v.resources='1 3273182 240000000 1500 245000 25,2 3273236 240000000 1500 245000 25,3 3273280 240000000 1500 245000 25,4 166973998 280000000 1500 3675000 325', v.cp='2941.8712 1867', v.crop_consumption=v.crop_consumption+1, v.troops_num='-1:11 0,12 0,13 0,14 9628,15 0,16 0,17 0,18 0,19 0,20 0,99 0', v.last_update_date=NOW() WHERE v.id='20899'



Count: 1  Time=2.97s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), ***[***]@localhost
  UPDATE p_villages v
  SET
  v.update_key='38c47',
  v.resources='1 827064241 840000000 1500 800 0,2 814159650 840000000 1500 800 0,3 801255060 840000000 1500 800 0,4 839968830 840000000 1500 1200 0',
  v.cp='27.304202777778 1106',
  v.last_update_date=NOW()
  WHERE
  v.id='1084' AND v.player_id='22290'

example of query is :

db::query("UPDATE p_villages v SET v.resources=:res, v.cp=:cp, v.crop_consumption=v.crop_consumption+$troops_crop_consumption, v.troops_num=:tnum, v.last_update_date=NOW() WHERE v.id=:id", array(
            'res' => $this->_getResourcesString($resultArr['resources']),
            'cp' => $resultArr['cp']['cpValue'] . " " . $resultArr['cp']['cpRate'],
            'tnum' => $troopTrainingStr,
            'id' => intval($taskRow['village_id'])
        ));

id is indexed

updates : //

this is the result of explain

Your SQL query has been executed successfully.

explain UPDATE p_villages v
  SET
  v.update_key='38c47',
  v.resources='1 827064241 840000000 1500 800 0,2 814159650 840000000 1500 800 0,3 801255060 840000000 1500 800 0,4 839968830 840000000 1500 1200 0',
  v.cp='27.304202777778 1106',
  v.last_update_date=NOW()
  WHERE
  v.id='1084' AND v.player_id='1'



1   UPDATE  v   
    NULL
    range   PRIMARY,NewIndex2,NewIndex6 PRIMARY 4   const   1   100.00  Using where 

this is much more simple query , that takes 2.39s ..

Count: 5  Time=2.39s (11s)  Lock=0.00s (0s)  Rows=0.0 (0), ***[***]@localhost
  UPDATE g_settings gs SET gs.qlocked=1, qlocked_date=NOW() WHERE gs.qlocked=0

as requested this is the result of query SHOW CREATE TABLE p_villages

p_villages  CREATE TABLE `p_villages` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `rel_x` smallint(6) DEFAULT NULL,
  `rel_y` smallint(7) DEFAULT NULL,
  `field_maps_id` tinyint(4) DEFAULT NULL,
  `rand_num` int(11) DEFAULT NULL,
  `image_num` tinyint(4) DEFAULT NULL,
  `parent_id` int(6) DEFAULT NULL,
  `tribe_id` tinyint(1) DEFAULT NULL,
  `player_id` int(5) DEFAULT NULL,
  `alliance_id` int(5) DEFAULT NULL,
  `player_name` varchar(15) DEFAULT NULL,
  `village_name` varchar(35) DEFAULT NULL,
  `alliance_name` varchar(25) DEFAULT NULL,
  `is_capital` tinyint(1) DEFAULT 0,
  `is_special_village` tinyint(1) DEFAULT 0,
  `is_oasis` tinyint(1) DEFAULT NULL,
  `people_count` int(11) DEFAULT 2,
  `crop_consumption` bigint(16) DEFAULT 2,
  `time_consume_percent` float DEFAULT 100,
  `offer_merchants_count` tinyint(4) DEFAULT 0,
  `resources` text DEFAULT NULL,
  `cp` varchar(300) DEFAULT NULL,
  `buildings` text DEFAULT NULL,
  `troops_training` text DEFAULT NULL,
  `troops_num` text DEFAULT NULL,
  `troops_out_num` text DEFAULT NULL,
  `troops_intrap_num` text DEFAULT NULL,
  `troops_out_intrap_num` text DEFAULT NULL,
  `troops_trapped_num` int(11) DEFAULT 0,
  `allegiance_percent` int(11) DEFAULT 100,
  `child_villages_id` text DEFAULT NULL,
  `village_oases_id` text DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `update_key` varchar(5) DEFAULT NULL,
  `last_update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NewIndex2` (`player_id`),
  KEY `NewIndex3` (`is_special_village`),
  KEY `NewIndex4` (`is_oasis`),
  KEY `NewIndex5` (`people_count`),
  KEY `NewIndex1` (`village_name`),
  KEY `NewIndex6` (`player_id`,`is_oasis`)
) ENGINE=InnoDB AUTO_INCREMENT=22501 DEFAULT CHARSET=utf8mb3    

Answer 1

Is it ENGINE=InnoDB? If not then that can be a big problem. Please provide SHOW CREATE TABLE p_villages

You say id is indexed but is it the PRIMARY KEY. A PRIMARY KEY is, by definition, unique. Hence only one row is involved, and the query should be very fast (a few milliseconds).

Are other queries running at the same time? They could be blocking access to the one row.

Adding RAM, plus increasing innodb_buffer_pool_size won't help much; "3 seconds" is some other problem. What value do you have for that setting?

If id is unique, why do you also test player_id in WHERE v.id='1084' AND v.player_id='1' (There is no problem here, just a puzzle.)

If id is unique, the one of these will succeed, one will fail:

v.id='1084' AND v.player_id='22290'
v.id='1084' AND v.player_id='1'

"everything is indexed" -- The query sets 4 columns; this implies that 4 indexes need to be updated. (Still, this would only take a few extra milliseconds.)