title: Understanding how index works
body: <p>Table definition <br></p>
  <pre><code>CREATE TABLE `prospectos` (
    `provincia` tinyint(3) unsigned NOT NULL,
    `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
    `nombre` varchar(60) COLLATE utf8_bin NOT NULL,
    `telefono_fijo` varchar(15) COLLATE utf8_bin NOT NULL,
    `telefono_movil` varchar(15) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`,`provincia`),
    KEY `nombre_idx` (`nombre`),
    KEY `tel_fijo_idx` (`provincia`,`telefono_fijo`) USING BTREE,
    KEY `tel_movil_idx` (`provincia`,`telefono_movil`) USING BTREE
  ) ENGINE=InnoDB AUTO_INCREMENT=35142 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
   PARTITION BY RANGE (`provincia`)
  (PARTITION `p01` VALUES LESS THAN (2) ENGINE = InnoDB,
  ..
  ..
  ..
  PARTITION `p24` VALUES LESS THAN (25) ENGINE = InnoDB,
  PARTITION `p99` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
  </code></pre>
  <p>doing this search is using both fields in primary key <br></p>
  <p>explain format=json select * from prospectos where provincia = 20 and id = 23;<br></p>
  <pre><code>query_block&quot;: {
      &quot;select_id&quot;: 1,
      &quot;table&quot;: {
        &quot;table_name&quot;: &quot;prospectos&quot;,
        &quot;partitions&quot;: [&quot;p20&quot;],
        &quot;access_type&quot;: &quot;const&quot;,
        &quot;possible_keys&quot;: [&quot;PRIMARY&quot;, &quot;tel_fijo_idx&quot;, &quot;tel_movil_idx&quot;],
        &quot;key&quot;: &quot;PRIMARY&quot;,
        &quot;key_length&quot;: &quot;5&quot;,
        &quot;used_key_parts&quot;: [&quot;id&quot;, &quot;provincia&quot;],
        &quot;ref&quot;: [&quot;const&quot;, &quot;const&quot;],
        &quot;rows&quot;: 1,
        &quot;filtered&quot;: 100
  </code></pre>
  <p>Now doing another select to use a different key why is not using both fields?<br><br></p>
  <p>explain format=json select * from prospectos where provincia = 20 and telefono_fijo = 3424527000;<br></p>
  <pre><code>&quot;query_block&quot;: {
      &quot;select_id&quot;: 1,
      &quot;table&quot;: {
        &quot;table_name&quot;: &quot;prospectos&quot;,
        &quot;partitions&quot;: [&quot;p20&quot;],
        &quot;access_type&quot;: &quot;ref&quot;,
        &quot;possible_keys&quot;: [&quot;tel_fijo_idx&quot;, &quot;tel_movil_idx&quot;],
        &quot;key&quot;: &quot;tel_fijo_idx&quot;,
        &quot;key_length&quot;: &quot;1&quot;,
        &quot;used_key_parts&quot;: [&quot;provincia&quot;],
        &quot;ref&quot;: [&quot;const&quot;],
        &quot;rows&quot;: 16042,
        &quot;filtered&quot;: 100,
        &quot;attached_condition&quot;: &quot;prospectos.telefono_fijo = 3424527000&quot;
  </code></pre>
  <p>It use only provincia on tel_fijo_idx and not telefono_fijo for searching.
  <br></p>
  <p>Why this search is not using both fields???</p>