title: Same query on local/live site, vastly different performance
body: <p>My slow query log is showing 5,6+ second runs randomly of a seemingly normal query. Running the same query on my local clone of the site gives a 0.05s query run time. Here is the EXPLAIN of both:</p>
  <p><a href="https://i.stack.imgur.com/rXCZR.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rXCZR.jpg" alt="enter image description here" /></a></p>
  <p>I'm a bit dense when it comes to MySQL optimization but, I don't understand how this can give such different results? The tables are roughly the same, the live site is more updated but otherwise very similar.</p>
  <hr />
  <p><strong>Edit:</strong></p>
  <p>Instead of comparing to my local installation, I instead am comparing to my &quot;old&quot; live server. I just moved servers this morning.</p>
  <p><strong>The OLD server DDL of the table:</strong></p>
  <pre><code>CREATE TABLE `wp_bp_activity` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `user_id` bigint(20) NOT NULL,
   `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
   `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
   `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
   `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
   `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
   `item_id` bigint(20) NOT NULL,
   `secondary_item_id` bigint(20) DEFAULT NULL,
   `date_recorded` datetime NOT NULL,
   `hide_sitewide` tinyint(1) DEFAULT 0,
   `mptt_left` int(11) NOT NULL DEFAULT 0,
   `mptt_right` int(11) NOT NULL DEFAULT 0,
   `is_spam` tinyint(1) NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`),
   KEY `date_recorded` (`date_recorded`),
   KEY `user_id` (`user_id`),
   KEY `item_id` (`item_id`),
   KEY `secondary_item_id` (`secondary_item_id`),
   KEY `component` (`component`),
   KEY `type` (`type`),
   KEY `mptt_left` (`mptt_left`),
   KEY `mptt_right` (`mptt_right`),
   KEY `hide_sitewide` (`hide_sitewide`),
   KEY `is_spam` (`is_spam`),
   FULLTEXT KEY `content` (`content`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1060622 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  </code></pre>
  <p><strong>The NEW server DDL:</strong></p>
  <pre><code>CREATE TABLE `wp_bp_activity` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `user_id` bigint(20) NOT NULL,
   `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
   `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
   `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
   `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
   `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
   `item_id` bigint(20) NOT NULL,
   `secondary_item_id` bigint(20) DEFAULT NULL,
   `date_recorded` datetime NOT NULL,
   `hide_sitewide` tinyint(1) DEFAULT 0,
   `mptt_left` int(11) NOT NULL DEFAULT 0,
   `mptt_right` int(11) NOT NULL DEFAULT 0,
   `is_spam` tinyint(1) NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`),
   KEY `date_recorded` (`date_recorded`),
   KEY `user_id` (`user_id`),
   KEY `item_id` (`item_id`),
   KEY `secondary_item_id` (`secondary_item_id`),
   KEY `component` (`component`),
   KEY `type` (`type`),
   KEY `mptt_left` (`mptt_left`),
   KEY `mptt_right` (`mptt_right`),
   KEY `hide_sitewide` (`hide_sitewide`),
   KEY `is_spam` (`is_spam`),
   FULLTEXT KEY `content` (`content`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1060840 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  </code></pre>
  <p>They are the same, but as you can see at the bottom of each the number of rows (by the AUTO_INCREMENT). They are very similar. As I said this OLD server was active this morning, so there are many recent datetimes here as well.</p>
  <p>Running the query pictured above on both servers however, results in the same difference in &quot;EXPLAIN&quot; output, the NEW server is examining an insane amount of rows in 10x the time.</p>