title: Strange MySQL index used
body: <p>I'm using MySQL 8 and I still have some doubts on how is choosing the index to be used on different queries</p>
  
  <p>Table looks like:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE IF NOT EXISTS `collection_stats` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `org_uuid` BINARY(16) NOT NULL,
    `project_uuid` BINARY(16) NOT NULL,
    `collection_name` VARCHAR(255) NOT NULL,
    `counter1` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `counter2` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `counter3` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `date_index` (`date` ASC),
    INDEX `org_usage_index` (`org_uuid` ASC, `date` DESC),
    INDEX `project_usage_index` (`org_uuid` ASC, `project_uuid` ASC, `date` DESC),
    UNIQUE INDEX `collection_usage_index` (`org_uuid` ASC, `project_uuid` ASC, `collection_name` ASC, `date` DESC))
  ENGINE = InnoDB;
  </code></pre>
  
  <p>Idea is that I have some daily stats organized by organization, project, collection (an organization can have multiple projects and a project multiple collections).</p>
  
  <p>I want to see the value of <code>counter1</code>, <code>counter 2</code> and <code>counter 3</code> per organization, per project or per collection in the last X days.</p>
  
  <h2>Getting the stats for an organization:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>explain SELECT 
      org_uuid, project_uuid, collection_name, 
      counter1, counter2, counter3 
  FROM collection_stats 
  WHERE 
      org_uuid = UUID_TO_BIN('bb5c2330-1a85-11e9-8ad9-b728a2ed4173', 1)  AND 
      date &gt;= DATE_SUB(NOW(), INTERVAL 10 DAY)
  </code></pre>
  
  <pre><code># id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
  '1', 'SIMPLE', 'collection_stats', NULL, 'ref', 'collection_usage_index,date_index,org_usage_index,project_usage_index', 'collection_usage_index', '16', 'const', '8', '100.00', 'Using index condition'
  </code></pre>
  
  <p>I see it's using the <code>collection_usage_index</code> instead <code>org_usage_index</code> but I have no explanation for it.</p>
  
  <h2>Getting the stats at project level</h2>
  
  <pre class="lang-sql prettyprint-override"><code>explain SELECT org_uuid, project_uuid, collection_name, counter1, counter2, counter3 FROM collection_stats 
  WHERE 
      org_uuid = UUID_TO_BIN('bb5c2330-1a85-11e9-8ad9-b728a2ed4173', 1) AND
      project_uuid = UUID_TO_BIN('faafad18-1a85-11e9-8a1d-b7281ece80f6', 1) AND
      date &gt;= DATE_SUB(NOW(), INTERVAL 10 DAY)
  </code></pre>
  
  <p>Explain says the same . Using  <code>collection_usage_index</code> instead the <code>project_usage_index</code> ..</p>
  
  <p>Any logical explanation for this . My table has only few rows for testing ? Might be the cause ?</p>