1

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?!

  • 1
    "key_length": "1" vs "key_length": "1023" doesn't ring the bell? – Kondybas Sep 13 '18 at 21:25
  • Not really, can you spell it out for me? That's on the counts table, why would that affect the speed of the photos table select? Sorry, I'm a bit of a noob! – Codemonkey Sep 13 '18 at 21:50
  • 1
    Comparison over the strings is slower than over the INTs. INTs are compared directly in the one operation while strings needs collations to be applied and then to be compared char by char. All slowdown is because of WHERE countName = 'photos' – Kondybas Sep 13 '18 at 21:58
  • 2
    Can you try this?: UPDATE counts AS c CROSS JOIN (SELECT COUNT(*) AS count_phootos FROM photos) AS p SET c.countValue = p.count_photos WHERE countName = 'photos'; – ypercubeᵀᴹ Sep 13 '18 at 22:14
0

Change to:

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

Either have autocommit = OFF, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.