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 thanSELECTs
. 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 ... )
intoJOIN ... ON ...
. Such is possible inUPDATE
; see "multi-table UPDATE".For more discussion of your specific cases, please provide
SHOW CREATE TABLE
andEXPLAIN SELECT ...
.I'm not saying that the Oracle branch necessarily does things better; rather that there could be a difference.