Why is this very simple UPDATE so much slower than the SELECT it contains? In MariaDB 10.3.9:

SELECT COUNT(*) FROM photos

vs

UPDATE counts SET countValue = (SELECT COUNT(*) FROM photos) WHERE countName = 'photos';

The select takes 1.1s vs the update which takes 2.6s.  3million rows in table photos, 3 rows in table counts.

ANALYZE format=JSON output:

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 1299.6,
    "table": {
      "table_name": "photos",
      "access_type": "index",
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["photoId"],
      "r_loops": 1,
      "rows": 3178665,
      "r_rows": 3.25e6,
      "r_total_time_ms": 1085.7,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    }
  }
}


vs

{
  "query_block": {
    "select_id": 1,
    "r_total_time_ms": 2764.7,
    "table": {
      "update": 1,
      "table_name": "counts",
      "access_type": "range",
      "possible_keys": ["countName"],
      "key": "sCount",
      "key_length": "1023",
      "used_key_parts": ["countName"],
      "rows": 1,
      "r_rows": 1,
      "r_filtered": 100,
      "r_total_time_ms": 0.0756,
      "attached_condition": "counts.countName = 'photos'"
    },
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "r_loops": 1,
          "r_total_time_ms": 2764.6,
          "table": {
            "table_name": "photos",
            "access_type": "index",
            "key": "PRIMARY",
            "key_length": "4",
            "used_key_parts": ["photoId"],
            "r_loops": 1,
            "rows": 3178665,
            "r_rows": 3.25e6,
            "r_total_time_ms": 2541.8,
            "filtered": 100,
            "r_filtered": 100,
            "using_index": true
          }
        }
      }
    ]
  }
}


Why is the subquery in the update query so slow?  I can easily fix this by doing a separate manual update with the result of the select query, but I'd like to know why this is slowing down so much.

On further examination, even THIS takes over twice as long as the simple select:

SET @foo = (SELECT COUNT(*) FROM photos);

What?  Why?!
Change to:

SELECT @ct := COUNT(*) FROM photos;
UPDATE counts SET countValue = @ct WHERE countName = 'photos';


Either have autocommit = ON, or at least don't put both statements in the same transaction.  This is to avoid locking counts while counting.

Also, countName should be the PRIMARY KEY.