title: MySQL ORDER BY clause slowing query by 4x
body: <p>UPDATE: I have vastly simplified the calls below and made adjustments to the question.</p>
  <p>I'm trying to figure out how to make this query faster.</p>
  <p>My query retrieves person/communication information for individuals in a specific campaign (in this example, 4). I join the most recent communication and sort by the communication date_created value. Below is the SQL we use.</p>
  <p>Note I'm doing LEFT JOIN because only a handful of people may have conversations associated with them; I still want to return matching people based on the core select (e.g. if 5 people have conversations but there's 30 people, I still want to show all 30, but sorted by those with conversations, and by date on those conversations).</p>
  <p>Below is the FAST call that is only 350ms to execute; the problem is it's the equivalent of an INNER JOIN due to the AND clause and thus doesn't LEFT JOIN the person_main records.</p>
  <pre><code>SELECT *
      FROM person_main pm
      LEFT JOIN cms_conversation_member ccmem ON ccmem.member_object_record_id = pm.ID   
          AND ccmem.member_type_c = get_gcs('cms_conversation_member', 'member_type_c', 'internal')
          AND ccmem.member_object_type_sc = get_gss('OBJECT_BASE','PERSON')
          AND ccmem.member_object_subtype_sc = get_gss('OBJECT_PERSON','MAIN')
      LEFT JOIN cms_conversation_main convm ON convm.ID = ccmem.cms_conversation_main_ref_id
      LEFT JOIN cms_communication_main ccomm ON ccomm.cms_conversation_main_ref_id = convm.ID
          AND ccomm.comm_direction_c = get_gcs('cms_communication_main', 'comm_direction_c', 'incoming')
      WHERE 1 = 1       
      GROUP BY pm.ID
      ORDER BY ccomm.date_created
      LIMIT 0,30;
  </code></pre>
  <p>Here is the SQL EXPLAIN of the call</p>
  <pre><code>[
      {
          &quot;id&quot;: &quot;1&quot;,
          &quot;select_type&quot;: &quot;PRIMARY&quot;,
          &quot;table&quot;: &quot;ci&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;ref&quot;,
          &quot;possible_keys&quot;: &quot;UK_campaign_item&quot;,
          &quot;key&quot;: &quot;UK_campaign_item&quot;,
          &quot;key_len&quot;: &quot;6&quot;,
          &quot;ref&quot;: &quot;const,const,const&quot;,
          &quot;rows&quot;: &quot;1000&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using index; Using temporary; Using filesort&quot;
      },
      {
          &quot;id&quot;: &quot;1&quot;,
          &quot;select_type&quot;: &quot;PRIMARY&quot;,
          &quot;table&quot;: &quot;pm&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;eq_ref&quot;,
          &quot;possible_keys&quot;: &quot;PRIMARY,ID_UNIQUE,IDX_person_main_name_first,IDX_person_main_name_middle,IDX_person_main_name_last,IDX_person_main_name_nick&quot;,
          &quot;key&quot;: &quot;PRIMARY&quot;,
          &quot;key_len&quot;: &quot;4&quot;,
          &quot;ref&quot;: &quot;xxx.ci.item_object_ref_id&quot;,
          &quot;rows&quot;: &quot;1&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;null&quot;
      },
      {
          &quot;id&quot;: &quot;1&quot;,
          &quot;select_type&quot;: &quot;PRIMARY&quot;,
          &quot;table&quot;: &quot;ccomm&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;eq_ref&quot;,
          &quot;possible_keys&quot;: &quot;PRIMARY,UK_cms_communication_main_ID&quot;,
          &quot;key&quot;: &quot;PRIMARY&quot;,
          &quot;key_len&quot;: &quot;4&quot;,
          &quot;ref&quot;: &quot;func&quot;,
          &quot;rows&quot;: &quot;1&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using where&quot;
      },
      {
          &quot;id&quot;: &quot;2&quot;,
          &quot;select_type&quot;: &quot;DEPENDENT SUBQUERY&quot;,
          &quot;table&quot;: &quot;ccomm2&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;index&quot;,
          &quot;possible_keys&quot;: &quot;FK_cms_communication_main_cms_conversation_main_ref_id,IDX_cms_communication_main_comm_direction_c&quot;,
          &quot;key&quot;: &quot;IDX_cms_communication_main_date_created&quot;,
          &quot;key_len&quot;: &quot;5&quot;,
          &quot;ref&quot;: &quot;null&quot;,
          &quot;rows&quot;: &quot;2&quot;,
          &quot;filtered&quot;: &quot;40.0&quot;,
          &quot;Extra&quot;: &quot;Using where; Backward index scan&quot;
      },
      {
          &quot;id&quot;: &quot;2&quot;,
          &quot;select_type&quot;: &quot;DEPENDENT SUBQUERY&quot;,
          &quot;table&quot;: &quot;&lt;subquery3&gt;&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;eq_ref&quot;,
          &quot;possible_keys&quot;: &quot;&lt;auto_distinct_key&gt;&quot;,
          &quot;key&quot;: &quot;&lt;auto_distinct_key&gt;&quot;,
          &quot;key_len&quot;: &quot;6&quot;,
          &quot;ref&quot;: &quot;xxx.ccomm2.cms_conversation_main_ref_id,xxx.pm.ID&quot;,
          &quot;rows&quot;: &quot;1&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using where&quot;
      },
      {
          &quot;id&quot;: &quot;3&quot;,
          &quot;select_type&quot;: &quot;MATERIALIZED&quot;,
          &quot;table&quot;: &quot;ccmem&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;ref&quot;,
          &quot;possible_keys&quot;: &quot;IDX_cms_conversation_member,IDX_cms_conversation_member_member_type_c,FK_cms_conversation_member_cms_conversation_main_ref_id&quot;,
          &quot;key&quot;: &quot;IDX_cms_conversation_member&quot;,
          &quot;key_len&quot;: &quot;6&quot;,
          &quot;ref&quot;: &quot;const,const,xxx.pm.ID&quot;,
          &quot;rows&quot;: &quot;3&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using where&quot;
      }
  ]
  </code></pre>
  <p>Here is the call that returns all records (even people without communication), but it takes 2.4 seconds (6x slower than the above call). <strong>But this is the resulting data that I need.</strong></p>
  <pre><code>    SELECT *
      FROM person_main pm
      LEFT JOIN cms_conversation_member ccmem ON ccmem.member_object_record_id = pm.ID   
          AND ccmem.member_type_c = get_gcs('cms_conversation_member', 'member_type_c', 'internal')
          AND ccmem.member_object_type_sc = get_gss('OBJECT_BASE','PERSON')
          AND ccmem.member_object_subtype_sc = get_gss('OBJECT_PERSON','MAIN')
      LEFT JOIN cms_conversation_main convm ON convm.ID = ccmem.cms_conversation_main_ref_id
      LEFT JOIN cms_communication_main ccomm ON ccomm.cms_conversation_main_ref_id = convm.ID
          AND ccomm.comm_direction_c = get_gcs('cms_communication_main', 'comm_direction_c', 'incoming')
      WHERE 1 = 1       
      GROUP BY pm.ID
      ORDER BY ccomm.date_created
      LIMIT 0,30;
  </code></pre>
  <p>Here is the EXPLAIN for the above call:</p>
  <pre><code>[
      {
          &quot;id&quot;: &quot;1&quot;,
          &quot;select_type&quot;: &quot;PRIMARY&quot;,
          &quot;table&quot;: &quot;ci&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;ref&quot;,
          &quot;possible_keys&quot;: &quot;UK_campaign_item&quot;,
          &quot;key&quot;: &quot;UK_campaign_item&quot;,
          &quot;key_len&quot;: &quot;6&quot;,
          &quot;ref&quot;: &quot;const,const,const&quot;,
          &quot;rows&quot;: &quot;1000&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using index; Using temporary; Using filesort&quot;
      },
      {
          &quot;id&quot;: &quot;1&quot;,
          &quot;select_type&quot;: &quot;PRIMARY&quot;,
          &quot;table&quot;: &quot;pm&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;eq_ref&quot;,
          &quot;possible_keys&quot;: &quot;PRIMARY,ID_UNIQUE,IDX_person_main_name_first,IDX_person_main_name_middle,IDX_person_main_name_last,IDX_person_main_name_nick&quot;,
          &quot;key&quot;: &quot;PRIMARY&quot;,
          &quot;key_len&quot;: &quot;4&quot;,
          &quot;ref&quot;: &quot;xxx.ci.item_object_ref_id&quot;,
          &quot;rows&quot;: &quot;1&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;null&quot;
      },
      {
          &quot;id&quot;: &quot;1&quot;,
          &quot;select_type&quot;: &quot;PRIMARY&quot;,
          &quot;table&quot;: &quot;ccomm&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;eq_ref&quot;,
          &quot;possible_keys&quot;: &quot;PRIMARY,UK_cms_communication_main_ID&quot;,
          &quot;key&quot;: &quot;PRIMARY&quot;,
          &quot;key_len&quot;: &quot;4&quot;,
          &quot;ref&quot;: &quot;func&quot;,
          &quot;rows&quot;: &quot;1&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using where&quot;
      },
      {
          &quot;id&quot;: &quot;2&quot;,
          &quot;select_type&quot;: &quot;DEPENDENT SUBQUERY&quot;,
          &quot;table&quot;: &quot;ccomm2&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;index&quot;,
          &quot;possible_keys&quot;: &quot;FK_cms_communication_main_cms_conversation_main_ref_id,IDX_cms_communication_main_comm_direction_c&quot;,
          &quot;key&quot;: &quot;IDX_cms_communication_main_date_created&quot;,
          &quot;key_len&quot;: &quot;5&quot;,
          &quot;ref&quot;: &quot;null&quot;,
          &quot;rows&quot;: &quot;2&quot;,
          &quot;filtered&quot;: &quot;40.0&quot;,
          &quot;Extra&quot;: &quot;Using where; Backward index scan&quot;
      },
      {
          &quot;id&quot;: &quot;2&quot;,
          &quot;select_type&quot;: &quot;DEPENDENT SUBQUERY&quot;,
          &quot;table&quot;: &quot;&lt;subquery3&gt;&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;eq_ref&quot;,
          &quot;possible_keys&quot;: &quot;&lt;auto_distinct_key&gt;&quot;,
          &quot;key&quot;: &quot;&lt;auto_distinct_key&gt;&quot;,
          &quot;key_len&quot;: &quot;6&quot;,
          &quot;ref&quot;: &quot;xxx.ccomm2.cms_conversation_main_ref_id,xxx.pm.ID&quot;,
          &quot;rows&quot;: &quot;1&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using where&quot;
      },
      {
          &quot;id&quot;: &quot;3&quot;,
          &quot;select_type&quot;: &quot;MATERIALIZED&quot;,
          &quot;table&quot;: &quot;ccmem&quot;,
          &quot;partitions&quot;: &quot;null&quot;,
          &quot;type&quot;: &quot;ref&quot;,
          &quot;possible_keys&quot;: &quot;IDX_cms_conversation_member,IDX_cms_conversation_member_member_type_c,FK_cms_conversation_member_cms_conversation_main_ref_id&quot;,
          &quot;key&quot;: &quot;IDX_cms_conversation_member&quot;,
          &quot;key_len&quot;: &quot;6&quot;,
          &quot;ref&quot;: &quot;const,const,xxx.pm.ID&quot;,
          &quot;rows&quot;: &quot;3&quot;,
          &quot;filtered&quot;: &quot;100.0&quot;,
          &quot;Extra&quot;: &quot;Using where&quot;
      }
  ]
  </code></pre>
  <p>Here are the Table Creation scripts.</p>
  <p><strong>cms_communication_main</strong></p>
  <pre><code>SET NAMES 'utf8mb4';
  
  --
  -- Create table `cms_communication_main`
  --
  CREATE TABLE cms_communication_main (
      ID int NOT NULL AUTO_INCREMENT,
      date_created datetime NOT NULL,
      comm_id_external varchar(1024) NOT NULL,
      comm_platform_c smallint NOT NULL,
      comm_direction_c tinyint NOT NULL,
      comm_state_sc smallint NOT NULL,
      comm_timestamp datetime DEFAULT NULL,
      comm_details json DEFAULT NULL,
      cms_conversation_main_ref_id int NOT NULL,
      PRIMARY KEY (ID),
      UNIQUE INDEX UK_cms_communication_main_ID (ID)
  )
  ENGINE = INNODB,
  AUTO_INCREMENT = 6,
  AVG_ROW_LENGTH = 3276,
  CHARACTER SET utf8mb4,
  COLLATE utf8mb4_unicode_ci;
  
  --
  -- Create index `IDX_cms_communication_main_date_created` on table `cms_communication_main`
  --
  ALTER TABLE cms_communication_main
  ADD INDEX IDX_cms_communication_main_date_created (date_created);
  
  --
  -- Create index `IDX_cms_communication_main_comm_platform_c` on table `cms_communication_main`
  --
  ALTER TABLE cms_communication_main
  ADD INDEX IDX_cms_communication_main_comm_platform_c (comm_platform_c);
  
  --
  -- Create index `IDX_cms_communication_main_comm_direction_c` on table `cms_communication_main`
  --
  ALTER TABLE cms_communication_main
  ADD INDEX IDX_cms_communication_main_comm_direction_c (comm_direction_c);
  
  --
  -- Create index `IDX_cms_communication_main_comm_id_external` on table `cms_communication_main`
  --
  ALTER TABLE cms_communication_main
  ADD FULLTEXT INDEX IDX_cms_communication_main_comm_id_external (comm_id_external);
  
  --
  -- Create foreign key
  --
  ALTER TABLE cms_communication_main
  ADD CONSTRAINT FK_cms_communication_main_cms_conversation_main_ref_id FOREIGN KEY (cms_conversation_main_ref_id)
  REFERENCES cms_conversation_main (ID);
  </code></pre>
  <p><strong>cms_conversation_main</strong></p>
  <pre><code>SET NAMES 'utf8mb4';
  
  CREATE TABLE cms_conversation_main (
      ID int NOT NULL AUTO_INCREMENT,
      date_created datetime NOT NULL,
      conversation_type_c smallint NOT NULL,
      conversation_id_external varchar(995) DEFAULT '',
      owner_account_main_ref_id int NOT NULL,
      conversation_state_sc smallint NOT NULL,
      PRIMARY KEY (ID),
      UNIQUE INDEX UK_cms_conversation_main_ID (ID)
  )
  ENGINE = INNODB,
  AUTO_INCREMENT = 6,
  AVG_ROW_LENGTH = 47,
  CHARACTER SET utf8mb4,
  COLLATE utf8mb4_unicode_ci;
  
  --
  -- Create index `IDX_cms_conversation_main_date_created` on table `cms_conversation_main`
  --
  ALTER TABLE cms_conversation_main
  ADD INDEX IDX_cms_conversation_main_date_created (date_created);
  
  --
  -- Create foreign key
  --
  ALTER TABLE cms_conversation_main
  ADD CONSTRAINT FK_cms_conversation_main_owner_account_main_ref_id FOREIGN KEY (owner_account_main_ref_id)
  REFERENCES account_main (ID);
  
  </code></pre>
  <p><strong>cms_conversation_member</strong></p>
  <pre><code>SET NAMES 'utf8mb4';
  
  CREATE TABLE cms_conversation_member (
      ID int NOT NULL AUTO_INCREMENT,
      date_joined datetime NOT NULL,
      member_type_c smallint NOT NULL,
      member_originator tinyint(1) NOT NULL DEFAULT 0,
      member_object_type_sc smallint NOT NULL,
      member_object_subtype_sc smallint NOT NULL,
      member_object_record_id smallint NOT NULL,
      member_custom_id varchar(995) DEFAULT NULL,
      member_communication_key varchar(255) DEFAULT NULL,
      cms_conversation_main_ref_id int NOT NULL,
      PRIMARY KEY (ID),
      UNIQUE INDEX UK_cms_conversation_member_ID (ID)
  )
  ENGINE = INNODB,
  AUTO_INCREMENT = 11,
  AVG_ROW_LENGTH = 50,
  CHARACTER SET utf8mb4,
  COLLATE utf8mb4_unicode_ci;
  
  --
  -- Create index `IDX_cms_conversation_member_date_joined` on table `cms_conversation_member`
  --
  ALTER TABLE cms_conversation_member
  ADD INDEX IDX_cms_conversation_member_date_joined (date_joined);
  
  --
  -- Create index `IDX_cms_conversation_member` on table `cms_conversation_member`
  --
  ALTER TABLE cms_conversation_member
  ADD INDEX IDX_cms_conversation_member (member_object_type_sc, member_object_subtype_sc, member_object_record_id);
  
  --
  -- Create index `IDX_cms_conversation_member_member_type_c` on table `cms_conversation_member`
  --
  ALTER TABLE cms_conversation_member
  ADD INDEX IDX_cms_conversation_member_member_type_c (member_type_c);
  
  --
  -- Create index `IDX_cms_conversation_member_member_originator` on table `cms_conversation_member`
  --
  ALTER TABLE cms_conversation_member
  ADD INDEX IDX_cms_conversation_member_member_originator (member_originator);
  
  --
  -- Create index `IDX_cms_conversation_member_member_communication_key` on table `cms_conversation_member`
  --
  ALTER TABLE cms_conversation_member
  ADD INDEX IDX_cms_conversation_member_member_communication_key (member_communication_key);
  
  --
  -- Create foreign key
  --
  ALTER TABLE cms_conversation_member
  ADD CONSTRAINT FK_cms_conversation_member_cms_conversation_main_ref_id FOREIGN KEY (cms_conversation_main_ref_id)
  REFERENCES cms_conversation_main (ID);
  
  </code></pre>
  <p>Any idea why the second call takes 5-6x longer than the first? Why is MySQL &quot;lagging&quot; so much on left joins that don't match the communications from the person_main table?</p>