UPDATE does not use INDEX, but SELECT does

I'm actually struggling understanding the problem here. I read everywhere saying that UPDATE benefits from indexes on the WHERE clause.

However, this query,

UPDATE `documents` SET `read`="1" WHERE `docid` IN ()

does not seem to be using an index.
The table documents has an index on docid and on read.

When I do an EXPLAIN, I see possible_keys = NULL and rows = 8011008 (full table). The subquery does use keys and read the proper rows (2 rows).

In the other hand, this query:

SELECT * FROM `documents` WHERE `docid` IN ()

does use the index on docid and runs very quickly. It reads a few more rows than needed (as per EXPLAIN), but totally acceptable.

Is there any explanation for this?

I use MariaDB 10.

As a funny note (regarding SELECT), if in the ` I use a UNION, while the subquery seems to get the proper number of rows, it seems the primary query does NOT use the index and does a full table scan.

If in the UPDATE I use JOIN instead of IN, the indexes are properly used. I resolved my problems by using JOIN`.

To address the question "UPDATE does not use INDEX, but SELECT does"...

Until very recently, many UPDATEs were processed by different code than SELECTs. Recently, there was a unification in the Oracle branch. I don't think it has made its way into MariaDB yet.

Also, the construct IN ( SELECT ... ) has been optimized very poorly until 5.6. Again, MariaDB may or may not have yet included some of the 5.6/5.7 improvements in this area.

It is almost always better to turn ... IN ( SELECT ... ) into JOIN ... ON .... Such is possible in UPDATE; see "multi-table UPDATE".

For more discussion of your specific cases, please provide SHOW CREATE TABLE and EXPLAIN SELECT ....

I'm not saying that the Oracle branch necessarily does things better; rather that there could be a difference.

StackExchange Database Administrators Q#153293, answer score: 5