Materialized Subquery Not Optimized

Question

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.

Query:

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 :)

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.