I am having trouble optimizing a materialized subquery in MariaDb 10.0.15 and having difficultly understanding the documentation on whether I can even optimize this query any further.

This query takes a few seconds to run but it seems it should possible to run much quicker than that. I am using "id = IN(subquery)" but have also tried "EXISTS(subquery)". My understanding is that with this version of MariaDB it shouldn't make a difference, and both types of queries take 2+ seconds.


SELECT rem.id                 
FROM ri_email_messages rem
WHERE rem.id IN(
    SELECT remc.message_id
    FROM ri_email_messages_contacts remc  
      INNER JOIN ri_email_contacts rec ON remc.contact_id = rec.id
    WHERE rec.email = '[email protected]'

Explain result:

| id | select_type  | table       | type  | possible_keys                 | key        | key_len | ref         | rows   | filtered | Extra                                                        |
|  1 | PRIMARY      | <subquery2> | ALL   | distinct_key                  | NULL       | NULL    | NULL        |      2 |      100 |                                                              |
|  1 | PRIMARY      | rem         | index | PRIMARY                       | PRIMARY    | 4       | NULL        | 126156 |       75 | Using where; Using index; Using join buffer (flat, BNL join) |
|  2 | MATERIALIZED | rec         | ref   | PRIMARY,email                 | email      | 768     | const       |      1 |      100 | Using where; Using index                                     |
|  2 | MATERIALIZED | remc        | ref   | PRIMARY,contact_id,message_id | contact_id | 4       | orgs.rec.id |      2 |      100 | Using index                                                  |

My understanding is a materialized view creates a temporarily table with the result of the subquery, and the id values in the temp table are used to constrain the result in the top query.

If I run the subquery itself, it runs instantly.

If I replace the subquery just with the list of ids that are returned in the subquery, it runs instantly:

SELECT rem.id                 
FROM ri_email_messages rem
WHERE rem.id IN(105703,110676,111628,105844,105854,105877,110508);

What am I doing wrong here :)


1 Answer 1


I have now found the source of the issue. It is because my ri_email_messages table was using the Aria engine. I had done this because InnoDB did not support full text search at the time. In MariaDB > 10.0.5, you can now do fulltext search in InnoDB, so I have switched back.

The way to tell in the EXPLAIN statement is that the PRIMARY query was using type=index. This should be type=eq_ref, since it is constrained upon the primary key, but this wasn't happening with Aria. After switching to InnoDB, this correctly shows as type=eq_ref, and the row match went from 126156 to 1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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