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.
documents has an index on
docid and on
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