Moved site to new, similar server - same query is taking 10x longer? Edit:
Old server SHOW GLOBAL VARIABLES: https://pastebin.com/raw/8GPTpub0
And for the new (problematic) server: https://pastebin.com/raw/XnfbFzh7

This morning I moved my WP website from one server to another, similarly configured with MariaDB 10.5.8 on the old, and 10.5.9 on the new.
According to slow-query log, some queries are going very slow. Here is one.
SELECT DISTINCT a.id  FROM wp_bp_activity a  
WHERE a.type IN ( 'activity_update','created_group','new_avatar' ) 
  AND a.is_spam = 0 
  AND a.hide_sitewide = 0 
  AND a.type NOT IN ('activity_comment', 'last_activity') 
ORDER BY a.date_recorded DESC, a.id DESC LIMIT 0, 16;

It takes 5,6,7 seconds. Running the exact, same query on the old server, it takes 0.0002 seconds.
Here is an EXPLAIN from both servers:

Why is one &quot;index&quot; and the other &quot;index_merge&quot; using different keys? It's the same query on the same table.

Both tables are nearly identical, one was live earlier today so it has relatively updated information.
The OLD server DDL of the table:
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

The NEW server DDL:
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
The fast query avoids sorting the result by reading in date_recorded index order matching your ORDER BY. It filters the hard way, comparing your conditions in the WHERE clause to rows one by one, but once it gets enough matching rows to satisfy the LIMIT, it stops scanning the table.
Whereas the slow query tries to use indexes on a couple of columns, thinking this will help. But it ends up having to scan hundreds of thousands of rows to find those that match, then finds the set of rows that matched both index searches (that's the Using intersect note), and finally sorts the large set of rows the hard way, using a filesort, just to return 16 rows and discard the rest.
This is a good counter-example case that shows that searching by index isn't always the best strategy. But the MySQL cost-based optimzer is biased to think that searching by index is usually better.
In this case, you might want to use an index hint to make it choose the date_recorded index.