MYSQL Update query using a temporary table when an equivalent select query does not

Question

I have a MYSQL table Foo which has Primary key on id, and 2 other non primary keys on different columns. Fiddle "select" example

My actual table contains many millions of rows so the behaviour of the explain is different, ie. it uses an Index_Merge on the 2 non primary indexes.

When I run the following Explain Update statement:

explain UPDATE Foo
SET readyState = 1 
WHERE readyState = 0
AND productName = 'OpenAM'
LIMIT 30;

The Extra column contains "Using Temporary".

When I run the equivalent explain Select statement:

Explain Select id, productName, readyState
FROM Foo
WHERE readyState = 0
AND productName = 'OpenAM'
Limit 30;

The Extra column does not contain "Using Temporary".

The effect of this on my actual table is that when I update, there is a temporary table being created with several million rows as they are all matching the conditions of the update before the Limit 30 kicks in. The update takes 4-5 seconds whereas the select only takes ~0.001s as it does not create the temp table of the merged index. I understand that the Update will also need to update all 3 indexes (Primary + 2 non primary used in the query) but I would be shocked if it took 4 seconds to update 30 index rows in 3 indexes.

QUESTION: Is there a way to force the Update to not use the unneccessary Temporary table? I was under the impression that MYSQL treated an Update query plan the same way as a select.

If the temp table is required for Update and not for Select, why?

EDIT:

Show Create Table (removed a heap of columns since it is a very wide table):
    CREATE TABLE Item (
      ID int(11) NOT NULL AUTO_INCREMENT,
      ImportId int(11) NOT NULL,
      MerchantCategoryName varchar(200) NOT NULL,
      HashId int(11) DEFAULT NULL,
      Processing varchar(36) DEFAULT NULL,
      Status int(11) NOT NULL,
      AuditWho varchar(200) NOT NULL,
      AuditWhen datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (ID),
      KEY idx_Processing_Item (Processing),
      KEY idx_Status_Item (Status),
      KEY idx_MerchantCategoryName_Item (MerchantCategoryName),
      KEY fk_Import_Item (ImportId),
      KEY fk_Hash_Item (HashId),
      CONSTRAINT fk_Hash_Item FOREIGN KEY (HashId) REFERENCES Hash (ID),
      CONSTRAINT fk_Import_Item FOREIGN KEY (ImportId) REFERENCES Import (ID)
) ENGINE=InnoDB AUTO_INCREMENT=12004589 DEFAULT CHARSET=utf8

Update statement

    explain UPDATE Item
    SET Processing = 'd53dbc91-eef4-11e5-a3a6-06f88beef4f3',
        Status = 2,
        AuditWho = 'name',
        AuditWhen = now()
    WHERE EventId = 1
    AND Processing is null
    AND Status = 1
    LIMIT 30;

Results:

'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra',
'1','SIMPLE','Item','index_merge','idx_Processing_Item,idx_Status_Item,fk_Import_Item','idx_Processing_Item,idx_Status_Item,fk_Import_Item','111,4,4',\N,'1362610','Using intersect(idx_Processing_Item,idx_Status_Item,fk_Import_Item); Using where; Using temporary',

Select Query

    explain select ID from Item where Status = 1 and Processing is null and ImportId = 1 limit 30;

Results:

'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra',
'1','SIMPLE','Item','index_merge','idx_Processing_Item,idx_Status_Item,fk_ImportEvent_Item','idx_Processing_Item,idx_Status_Item,fk_Import_Item','111,4,4',\N,'1362610','Using intersect(idx_Processing_ItemPending,idx_Status_ItemPending,fk_ImportEvent_ItemPending); Using where; Using index',

Answer 1

A guess:

The UPDATE is changing an indexed value (readyState), correct? That means that the index in question is being changed as the UPDATE is using it? So, the UPDATE may be "protecting" itself by fetching the rows (in an inefficient way, apparently), tossing them into a tmp table, and only then performing the action.

"Index merge intersect" is almost always less efficient than a composite index: INDEX(readyState, productName) (in either order). Suggest you add that.

Since you have no ORDER BY, which "30" will be unpredictable. Suggest you add ORDER BY the-primary-key.