How to optimize this query with a self-join?
<p>I have the following table:</p>

<pre><code>CREATE TABLE lab_data (
  id int(11) NOT NULL,
  patient_sid int(11) DEFAULT NULL,
  double_value double DEFAULT NULL,
  string_value varchar(7) DEFAULT NULL,
  data_type_id int(11) DEFAULT NULL,
  event_date datetime DEFAULT NULL,
  attribute_id int(11) DEFAULT NULL,
  lft int(11) DEFAULT NULL,
  rgt int(11) DEFAULT NULL,
  parent int(11) DEFAULT NULL,
  num_children int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_bucket (attribute_id,string_value),
  KEY idx_test (attribute_id,double_value,event_date,patient_id,lft,rgt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</code></pre>

<p>This is a very large table (11 million rows), and I really need to optimize the following self-join query:</p>

<pre><code>SELECT  distinct(patient_sid) as patient_sid
FROM lab_data l1 
LEFT JOIN (SELECT patient_sid, lft, rgt
           FROM lab_data
           WHERE attribute_id = 36 AND double_value &gt;= 1.2 AND event_date &gt;= '1776-01-01' 
         ) AS l2 
ON l1. patient_sid = l2.patient_sid AND l1.lft &gt;= l2.lft AND l1.rgt &lt;= l2.rgt
WHERE l1.attribute_id = 33 AND l1.string_value = '2160-0' 
</code></pre>

<p>(I have tried moving the range search for <code>AND l1.lft &gt;= l2.lft AND l1.rgt &lt;= l2.rgt</code> into the outer where clause, and did not see much difference.)</p>

<p>The index, idx_bucket is correctly being used for the outer query, but the idx_test is not used for the inner sub query when I do an EXPLAIN query plan. Instead, it is using idx_bucket, too.</p>

<pre><code># id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'l1', NULL, 'ref', 'idx_bucket,idx_test', 'idx_bucket', '29', 'const,const', '517298', '100.00', 'Using temporary'
'1', 'SIMPLE', 'lab_data', NULL, 'ref', 'idx_bucket,idx_test', 'idx_bucket', '5', 'const', '13657', '100.00', 'Using where; Distinct'
</code></pre>

<p>If I force the inner subquery to use idx_test, I get the following query plan:</p>

<pre><code># id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'l1', NULL, 'ref', 'idx_bucket,idx_test', 'idx_bucket', '29', 'const,const', '517298', '100.00', 'Using temporary'
'1', 'SIMPLE', 'lab_data', NULL, 'ref', 'idx_test', 'idx_test', '5', 'const', '21808', '100.00', 'Using where; Distinct'
</code></pre>

<p>And from the JSON output, I only see <code>attribute_id</code> under <code>used_key_parts</code> used for this index ? According to the MySQL documentation (<a href="https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html" rel="nofollow">B-Tree Index Characteristics</a>), the btree indices are such that, "A B-tree index can be used for column comparisons in expressions that use the =, >, >=, &lt;, &lt;=, or BETWEEN operators." </p>

<pre><code> "table": {
  "table_name": "lab_data",
  "access_type": "ref",
  "possible_keys": [
    "idx_test"
  ],
  "key": "idx_test",
  "used_key_parts": [
    "attribute_id"
  ],
  "key_length": "5",
  "ref": [
    "const"
  ],
  "rows_examined_per_scan": 8898041,
  "rows_produced_per_join": 988473,
  "filtered": "11.11",
  "index_condition": "((`ns_large2_2016`.`lab_data`.`double_value` &gt;= 1.2) and (`ns_large2_2016`.`lab_data`.`event_date` &gt;= '1776-01-01'))",
  "cost_info": {
    "read_cost": "339069.00",
    "eval_cost": "197694.69",
    "prefix_cost": "2118677.20",
    "data_read_per_join": "82M"
  },
  "used_columns": [
    "patient_sid",
    "double_value",
    "event_date",
    "attribute_id",
    "lft",
    "rgt"
  ]
</code></pre>

<p>Am I misunderstanding what <code>used_key_parts</code> is? I am assuming that these are the columns of the index being used. The documentation for b-tree indexes makes me believe that range comparisons should be included.</p>