MySQL – Why UPDATE Does Not Use INDEX but SELECT Does

indexinnodbmariadbmariadb-10.1MySQL

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 (<subquery>)

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 (<subquery>)

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 <subquery> 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.

Best Answer

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.