0

Consider this UPDATE statement:

UPDATE `messages` force index (primary)
SET `isDeleted`=1
WHERE `messages`.`id` = '069737b6-726d-4f5b-a5b9-0510acdd7a92';

Here's the explain graph for it:

explain graph screenshot

Why this simple query uses index range scan instead of single row fetch or at least unique key fetch? Notice that I use FORCE INDEX and exactly same query written as SELECT statement results in "Single Row (constant)" scan.

Also same happens if I add LIMIT 1

I'm using mysql 5.6.46

Ilia Sidorenko
  • 2,157
  • 3
  • 26
  • 30
  • What's the primary key for 'messages'? – Gilbert Le Blanc Mar 12 '20 at 22:44
  • Primary key for `messages` is `id` – Ilia Sidorenko Mar 12 '20 at 22:45
  • I'm not 100% sure, but my initial instinct would guess it has to do with the data type of id being a CHAR. – Uueerdo Mar 12 '20 at 22:45
  • But if I do `select id from messages where id = '069737b6-726d-4f5b-a5b9-0510acdd7a92';` then the scan method is "Single row (constant)". Why is it different for `SELECT` statement with the same `WHERE` part? – Ilia Sidorenko Mar 12 '20 at 22:47
  • 2
    Since you are SETing the primary key value itself, it may be reporting the scan it must make to determine you are not setting it to non-unique value. What is the graph if you set a different field instead? – Uueerdo Mar 12 '20 at 22:48
  • If I try `UPDATE messages force index (primary) SET isDeleted=1 WHERE messages.id = '069737b6-726d-4f5b-a5b9-0510acdd7a92';` it's still "Index Range Scan" – Ilia Sidorenko Mar 12 '20 at 22:50
  • I've used SET `id` for my example so that i'm selecting less attributes which sometimes affects index choice (I think) – Ilia Sidorenko Mar 12 '20 at 23:00
  • I've updated the question. – Ilia Sidorenko Mar 12 '20 at 23:02
  • I'm assuming you are aware of what's happening behind index range scan and others. Have you considered that scanning an indexed column is faster than other types of scan? – itwasntme Mar 12 '20 at 23:33
  • `force index` usually disables these mysql heuristics. I guess I found my answer here: https://stackoverflow.com/questions/41530165/how-to-force-mysql-update-query-to-use-index-how-to-enable-mysql-engine-to-auto#comment99312066_41531733 – Ilia Sidorenko Mar 12 '20 at 23:58

2 Answers2

3

MySQL ignores index hints in UPDATE statements.

Therefore there's no way to deterministically set the scan method for UPDATE query.

I guess I have to rely on MySQL's heuristics on deciding which scan method is faster based on table size, etc. Not ideal, because I don't know what's gonna be the performance profile for that query anymore, but I hope it will at least be "Index Range Scan" and nothing worse...

Reference: How to force mysql UPDATE query to use index? How to enable mysql engine to automatically use the index instead of forcing it?

https://dba.stackexchange.com/a/153323/146991

Ilia Sidorenko
  • 2,157
  • 3
  • 26
  • 30
2

The index hint is a red herring. I think it is because of internal differences between SELECT and UPDATE, especially when it comes to planning the query.

Suggest you file a bug.

I think it is not really doing a "range". You can get some confidence in this by doing:

FLUSH STATUS;
UPDATE ... ;
SHOW SESSION STATUS LIKE 'Handler%';

(I have checked a variety of versions; nothing hints that more than 1 row is being hit other than the dubious "range".)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • The included confidence tip helped a ton and the answer was perfectly direct. Thank you. The one thing I would say is make sure you are in a test environment, as the tip will actually execute the `update`. – Copy and Paste Aug 03 '22 at 20:10