title: Really slow OUTER JOIN on MariaDB compared to MySQL
body: <h2>Context</h2>
  <p>Consider this table containing friends from a specific user.</p>
  <pre><code>CREATE TABLE `ce_mutual_friends`
  (
      `user_id` mediumint unsigned NOT NULL,
      `friend_id` mediumint unsigned NOT NULL,
      PRIMARY KEY (`user_id`, `friend_id`),
      INDEX `friend_id_user_id` (`friend_id`, `user_id`)
     
  )ENGINE=InnoDB
  
  </code></pre>
  <p>Please note that the list is mutual. If user 1 is befriended with friend 2 then this automatically means that user 2 is befriended with friend 1. There are 10K rows in this table.</p>
  <h2>Objective</h2>
  <p>I want to have a list of users with:</p>
  <ul>
  <li>direct friends (relation_degree = 1 )</li>
  <li>friends of friends (relation_degree = 2 )</li>
  <li>friends of friends of friends (relation_degree = 3)</li>
  </ul>
  <p>I do this by performing multiple LEFT OUTER JOIN:</p>
  <pre><code> -- for total network
  WITH RECURSIVE cte(level, user_id, friend_id) AS(
    SELECT 1, user_id, friend_id
      FROM ce_mutual_friends
    UNION
    SELECT cte.level+1, cte.user_id, t2.friend_id
      FROM cte
        INNER JOIN ce_mutual_friends t2
          ON t2.user_id = cte.friend_id
      WHERE cte.level+1 &lt;= 3 
  )
  SELECT cte.user_id, cte.friend_id, min(cte.level)
    FROM cte
    GROUP BY cte.user_id, cte.friend_id;
    
    
  </code></pre>
  <p>This query is runned several times a day to perform some background tasks.</p>
  <h2>Problem</h2>
  <p>I was considering migrating from MySQL to MariaDB. On my development environment I use this <code>docker-compose.yml</code> to launch 2 containers.</p>
  <pre><code>version: '3'
  services:
    mysql:
      container_name: mysql_db
      restart: always
      image: mysql:latest
      ports: 
          - &quot;3306:3306&quot; 
      environment:
         - MYSQL_ROOT_PASSWORD=secret
      volumes:
        - ./mysql-data:/var/lib/mysql
    mariadb:
      container_name: mariadb
      restart: always
      image: mariadb:latest
      ports: 
          - &quot;3305:3306&quot; 
      environment:
         - MYSQL_ROOT_PASSWORD=secret
      volumes:
        - ./mariadb-data:/var/lib/mysql
  </code></pre>
  <p>I exported and imported my database from MySQL to MariaDB. I would expect that the query on MariaDB would be equal or slightly faster then on MySQL, but to my surprise MariaDB was far worst.</p>
  <ul>
  <li>MySQL 1.25sec</li>
  <li>MariaDB 26sec</li>
  </ul>
  <p>If I modify the query to only fetch for one specific user (this query actually runs very often in our application ) then the duration's are:</p>
  <ul>
  <li>MySQL 0.002sec</li>
  <li>MariaDB 0.015sec</li>
  </ul>
  <p>Here is the specific user adjusted query:</p>
  <pre><code> -- for user 1
  WITH RECURSIVE cte(level, user_id, friend_id) AS(
    SELECT 1, user_id, friend_id
      FROM ce_mutual_friends
      WHERE user_id = 1
    UNION
    SELECT cte.level+1, cte.user_id, t2.friend_id
      FROM cte
        INNER JOIN ce_mutual_friends t2
          ON t2.user_id = cte.friend_id
      WHERE cte.level+1 &lt;= 3 
  )
  SELECT cte.user_id, cte.friend_id, min(cte.level)
    FROM cte
    GROUP BY cte.user_id, cte.friend_id;
  </code></pre>
  <p><strong>EXPLAIN SELECT ON MySQL</strong></p>
  <pre><code>
  +------+----------------+-------------------+------------+-------+---------------+---------+---------+---------------+--------+----------+--------------------------+-------------------+
  |  id  |  select_type   |       table       | partitions | type  | possible_keys |   key   | key_len |      ref      |  rows  | filtered |          Extra           |                   |
  +------+----------------+-------------------+------------+-------+---------------+---------+---------+---------------+--------+----------+--------------------------+-------------------+
  | 1    | PRIMARY        | &lt;derived2&gt;        | NULL       | ALL   | NULL          | NULL    | NULL    | NULL          | 131532 | 100.00   | &quot;Using temporary&quot;        |                   |
  | 2    | DERIVED        | ce_mutual_friends | NULL       | index | NULL          | PRIMARY | 6       | NULL          | 10506  | 100.00   | &quot;Using index&quot;            |                   |
  | 3    | UNION          | cte               | NULL       | ALL   | NULL          | NULL    | NULL    | NULL          | 10506  | 100.00   | &quot;Recursive; Using where&quot; |                   |
  | 3    | UNION          | t2                | NULL       | ref   | PRIMARY       | PRIMARY | 3       | cte.friend_id | 11     | 100.00   | &quot;Using index&quot;            |                   |
  | NULL | &quot;UNION RESULT&quot; | &quot;&lt;union2          | 3&gt;&quot;        | NULL  | ALL           | NULL    | NULL    | NULL          | NULL   | NULL     | NULL                     | &quot;Using temporary&quot; |
  +------+----------------+-------------------+------------+-------+---------------+---------+---------+---------------+--------+----------+--------------------------+-------------------+
  
  
  +-------------------------------------+----------------+
  |            Variable_name            |     Value      |
  +-------------------------------------+----------------+
  | bulk_insert_buffer_size             | 8388608        |
  | innodb_buffer_pool_chunk_size       | 134217728      |
  | innodb_buffer_pool_dump_at_shutdown | ON             |
  | innodb_buffer_pool_dump_now         | OFF            |
  | innodb_buffer_pool_dump_pct         | 25             |
  | innodb_buffer_pool_filename         | ib_buffer_pool |
  | innodb_buffer_pool_in_core_file     | ON             |
  | innodb_buffer_pool_instances        | 1              |
  | innodb_buffer_pool_load_abort       | OFF            |
  | innodb_buffer_pool_load_at_startup  | ON             |
  | innodb_buffer_pool_load_now         | OFF            |
  | innodb_buffer_pool_size             | 134217728      |
  | innodb_change_buffer_max_size       | 25             |
  | innodb_change_buffering             | all            |
  | innodb_log_buffer_size              | 16777216       |
  | innodb_sort_buffer_size             | 1048576        |
  | join_buffer_size                    | 262144         |
  | key_buffer_size                     | 8388608        |
  | myisam_sort_buffer_size             | 8388608        |
  | net_buffer_length                   | 16384          |
  | preload_buffer_size                 | 32768          |
  | read_buffer_size                    | 131072         |
  | read_rnd_buffer_size                | 262144         |
  | select_into_buffer_size             | 131072         |
  | sort_buffer_size                    | 262144         |
  | sql_buffer_result                   | OFF            |
  +-------------------------------------+----------------+
  
  </code></pre>
  <p><strong>EXPLAIN SELECT &amp; Buffer varsON MariaBD</strong></p>
  <pre><code>
  +------+-------------------+-------------------+-------+---------------+---------+---------+-------------------------+-------+-----------------------------------+--+
  |  id  |    select_type    |       table       | type  | possible_keys |   key   | key_len |           ref           | rows  |               Extra               |  |
  +------+-------------------+-------------------+-------+---------------+---------+---------+-------------------------+-------+-----------------------------------+--+
  | 1    | PRIMARY           | &lt;derived2&gt;        | ALL   | NULL          | NULL    | NULL    | NULL                    | 10506 | &quot;Using temporary; Using filesort&quot; |  |
  | 2    | DERIVED           | ce_mutual_friends | index | NULL          | PRIMARY | 6       | NULL                    | 10506 | &quot;Using index&quot;                     |  |
  | 3    | &quot;RECURSIVE UNION&quot; | t2                | index | PRIMARY       | PRIMARY | 6       | NULL                    | 10506 | &quot;Using index&quot;                     |  |
  | 3    | &quot;RECURSIVE UNION&quot; | &lt;derived2&gt;        | ref   | key0          | key0    | 4       | mysite_prod_.t2.user_id | 10    | &quot;Using where&quot;                     |  |
  | NULL | &quot;UNION RESULT&quot;    | &quot;&lt;union2          | 3&gt;&quot;   | ALL           | NULL    | NULL    | NULL                    | NULL  | NULL                              |  |
  +------+-------------------+-------------------+-------+---------------+---------+---------+-------------------------+-------+-----------------------------------+--+
  
  
  +-------------------------------------+----------------+
  |            Variable_name            |     Value      |
  +-------------------------------------+----------------+
  | aria_pagecache_buffer_size          | 134217728      |
  | aria_sort_buffer_size               | 268434432      |
  | bulk_insert_buffer_size             | 8388608        |
  | innodb_buffer_pool_chunk_size       | 134217728      |
  | innodb_buffer_pool_dump_at_shutdown | ON             |
  | innodb_buffer_pool_dump_now         | OFF            |
  | innodb_buffer_pool_dump_pct         | 25             |
  | innodb_buffer_pool_filename         | ib_buffer_pool |
  | innodb_buffer_pool_load_abort       | OFF            |
  | innodb_buffer_pool_load_at_startup  | ON             |
  | innodb_buffer_pool_load_now         | OFF            |
  | innodb_buffer_pool_size             | 2147483648     |
  | innodb_change_buffer_max_size       | 25             |
  | innodb_change_buffering             | all            |
  | innodb_log_buffer_size              | 16777216       |
  | innodb_sort_buffer_size             | 1048576        |
  | join_buffer_size                    | 262144         |
  | join_buffer_space_limit             | 2097152        |
  | key_buffer_size                     | 134217728      |
  | mrr_buffer_size                     | 262144         |
  | myisam_sort_buffer_size             | 134216704      |
  | net_buffer_length                   | 16384          |
  | preload_buffer_size                 | 32768          |
  | read_buffer_size                    | 131072         |
  | read_rnd_buffer_size                | 262144         |
  | sort_buffer_size                    | 2097152        |
  | sql_buffer_result                   | OFF            |
  +-------------------------------------+----------------+
  
  </code></pre>
  <ol>
  <li>I have tested these queries on MySQL Workbench. By my understanding this shouldn't make any difference. But please correct me if I am wrong.</li>
  <li>How come that MariaDB so much slower then MySQL? Using <code>docker stats</code> and <code>htop</code> I see that there is no RAM shortage and 1 cpu core spikes for the duration of the query.</li>
  </ol>