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.

  • 1
    The MySQL optimizer is known to treat sub-queries badly (in general and especially with DML statements) Oct 25 '16 at 18:13

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.

  • What you say makes sense! Thank you very much :)
    – Nuno
    Oct 26 '16 at 20:58
  • amazing answer! now people at work think i'm smart! :)
    – ufk
    Aug 14 '17 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.