title: MySQL - JOIN BETWEEN - Range checked for each record (index map: 0x7)
body: <p>The following query results in "Range checked for each record (index map: 0x7)".
  I would expect because there is a lft + rght index it would use this index. I already found on article <a href="https://stackoverflow.com/questions/13254023/mysql-range-check-instead-of-index-usage-on-inner-join">Mysql range check instead of index usage on inner join</a> but this table is using its own structure so the exact same column types. I use MySQL 5.7.</p>
  
  <pre><code>DROP TABLE IF EXISTS test.tree;
  CREATE TABLE test.tree (
      lft INT(10) UNSIGNED NOT NULL,
      rght INT(10) UNSIGNED NOT NULL,
      INDEX ix_left_right (lft, rght),
      INDEX ix_left (lft),
      INDEX ix_right (rght)
  );
  
  EXPLAIN
  SELECT X.*
  FROM test.tree X
  LEFT JOIN test.tree Y ON  X.lft BETWEEN Y.lft AND Y.rght;
  </code></pre>
  
  <p>Result</p>
  
  <pre><code>{
      "table": "UnknownTable",
      "rows":
      [
          {
              "id": 1,
              "select_type": "SIMPLE",
              "table": "X",
              "partitions": null,
              "type": "index",
              "possible_keys": null,
              "key": "ix_left_right",
              "key_len": "8",
              "ref": null,
              "rows": 1,
              "filtered": 100.00,
              "Extra": "Using index"
          },
          {
              "id": 1,
              "select_type": "SIMPLE",
              "table": "Y",
              "partitions": null,
              "type": "ALL",
              "possible_keys": "ix_left_right,ix_left,ix_right",
              "key": null,
              "key_len": null,
              "ref": null,
              "rows": 1,
              "filtered": 100.00,
              "Extra": "Range checked for each record (index map: 0x7)"
          }
      ]
  }
  </code></pre>
  
  <p>Does anyone know what I`m doing wrong?</p>