title: How do I improve performance on a DISTINCT select across three joined tables?
body: <p>I have the following tables in question:</p>
  
  <ul>
  <li>Personas</li>
  <li>ImpressionsPersonas  [join table - Personas ManyToMany Impressions]</li>
  <li>Impressions</li>
  </ul>
  
  <p>My query looks like this, the EXPLAIN results are attached below:</p>
  
  <pre><code>   SELECT
  
     DISTINCT (Personas.id),
      Personas.parent_id, 
     Personas.persona, 
     Personas.subpersonas_count, 
     Personas.is_subpersona, 
     Personas.impressions_count, 
     Personas.created, 
     Personas.modified
  
  
     FROM personas as Personas 
  
     INNER JOIN   
          impressions_personas ImpressionsPersonas ON (
          Personas.id = ImpressionsPersonas.persona_id
      )
     inner JOIN impressions Impressions ON (Impressions.id = ImpressionsPersonas.impression_id AND Impressions.timestamp &gt;= "2016-06-01 00:00:00" AND Impressions.timestamp &lt;= "2016-07-31 00:00:00") 
  </code></pre>
  
  <p><strong>EXPLAIN</strong></p>
  
  <pre><code>    +----+-------------+---------------------+--------+-----------------------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+-----------------------+
      | id | select_type | table               | type   | possible_keys                                                         | key         | key_len | ref                                         | rows | filtered | Extra                 |
      +----+-------------+---------------------+--------+-----------------------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+-----------------------+
      |  1 | SIMPLE      | Personas            | ALL    | PRIMARY                                                               | NULL        | NULL    | NULL                                        |  159 |   100.00 | Using temporary       |
      |  1 | SIMPLE      | ImpressionsPersonas | ref    | impression_idx,persona_idx,comp_imp_persona,comp_imp_pri,comp_per_pri | persona_idx | 8       | gen1_d2go.Personas.id                       |  396 |   100.00 | Distinct              |
      |  1 | SIMPLE      | Impressions         | eq_ref | PRIMARY,timestamp,timestamp_id                                        | PRIMARY     | 8       | gen1_d2go.ImpressionsPersonas.impression_id |    1 |   100.00 | Using where; Distinct |
      +----+-------------+---------------------+--------+-----------------------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+-----------------------+
      3 rows in set, 1 warning (0.00 sec)
  </code></pre>
  
  <p><strong>CREATE STATEMENT FOR PERSONAS</strong></p>
  
  <pre><code>CREATE TABLE `personas` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `parent_id` bigint(20) unsigned DEFAULT NULL,
    `persona` varchar(150) NOT NULL,
    `subpersonas_count` int(10) unsigned DEFAULT '0',
    `is_subpersona` tinyint(1) unsigned DEFAULT '0',
    `impressions_count` bigint(20) unsigned DEFAULT '0',
    `created` datetime DEFAULT NULL,
    `modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `lookup` (`parent_id`,`persona`),
    KEY `parent_index` (`parent_id`),
    KEY `persona` (`persona`),
    KEY `persona_a_id` (`id`,`persona`),
    CONSTRAINT `self_referential_join_to_self` FOREIGN KEY (`parent_id`) REFERENCES `personas` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=1049 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p><strong>CREATE STATEMENT FOR IMPRESSIONS_PERSONAS</strong></p>
  
  <pre><code>CREATE TABLE `impressions_personas` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `impression_id` bigint(20) unsigned NOT NULL,
    `persona_id` bigint(20) unsigned NOT NULL,
    `created` datetime DEFAULT NULL,
    `modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `impression_idx` (`impression_id`),
    KEY `persona_idx` (`persona_id`),
    KEY `comp_imp_persona` (`impression_id`,`persona_id`),
    KEY `comp_imp_pri` (`impression_id`,`id`),
    KEY `comp_per_pri` (`persona_id`,`id`),
    CONSTRAINT `impression` FOREIGN KEY (`impression_id`) REFERENCES `impressions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `persona` FOREIGN KEY (`persona_id`) REFERENCES `personas` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=19387839 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p><strong>CREATE STATEMENT FOR IMPRESSIONS</strong></p>
  
  <pre><code>CREATE TABLE `impressions` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `device_id` bigint(20) unsigned NOT NULL,
    `beacon_id` bigint(20) unsigned NOT NULL,
    `zone_id` bigint(20) unsigned NOT NULL,
    `application_id` bigint(20) unsigned DEFAULT NULL,
    `timestamp` datetime NOT NULL,
    `google_place_id` bigint(20) unsigned DEFAULT NULL,
    `name` varchar(60) DEFAULT NULL,
    `lat` decimal(15,10) DEFAULT NULL,
    `lng` decimal(15,10) DEFAULT NULL,
    `personas_count` int(10) unsigned DEFAULT '0',
    `created` datetime DEFAULT NULL,
    `modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `device_idx` (`device_id`),
    KEY `zone_idx` (`zone_id`),
    KEY `beacon_id_idx2` (`beacon_id`),
    KEY `timestamp` (`timestamp`),
    KEY `appid_fk_idx_idx` (`application_id`),
    KEY `comp_lookup` (`device_id`,`beacon_id`,`timestamp`),
    KEY `timestamp_id` (`timestamp`,`id`),
    CONSTRAINT `appid_fk_idx` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `beacon_id` FOREIGN KEY (`beacon_id`) REFERENCES `beacons` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `device2` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `zone_FK` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=1582724 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <hr>
  
  <p>Now - when I run the query without the DISTINCT and using a <code>COUNT(*)</code>, it pulls about 17,000,000 records. Running it with DISTINCT yields 112 records. I am not sure why there are so many records showing up when the explain showed only 159 and 396.</p>
  
  <p>Some information about the tables:</p>
  
  <p>The Personas table contains 159 records. The ImpressionsPersonas table contains about 12.6 million, and Impressions contains about 920,000 records.</p>
  
  <p>What we are doing is selecting the <code>Personas</code> table and joining to the Impressions by way of the join table <code>ImpressionsPersonas</code>. There are filters applied to the <code>Impressions</code> table (date in this case). </p>
  
  <p>Note: removing the date filter had a negligible impact on the execution time - which hovers right around 120s. Is there a way to filter these records down to cut down the execution time of this query?</p>