I'm having some trouble diagnosing this very simple query which seems quite slow to me, any help would be much appreciated.
The table of relevance for the query at issue is defined as follows:
CREATE TABLE `Episode` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `podcast_id` int(10) unsigned NOT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `air_date` datetime NOT NULL, `episode_designation` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `length` int(11) DEFAULT NULL, `audio_quality` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `description` mediumtext COLLATE utf8mb4_unicode_ci, `image_url` mediumtext COLLATE utf8mb4_unicode_ci, `audio_url` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `file_size` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `episode_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_explicit` tinyint(1) NOT NULL DEFAULT '0', `description_sanitized` longtext COLLATE utf8mb4_unicode_ci, `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `availability` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `media_info` longtext COLLATE utf8mb4_unicode_ci, `guid` mediumtext COLLATE utf8mb4_unicode_ci, `visibility` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'visible', `source` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'rss', `meta_data` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`), KEY `Episode_podcast_id` (`podcast_id`), KEY `Episode_guid` (`guid`(191)), CONSTRAINT `_Episode_podcast_id` FOREIGN KEY (`podcast_id`) REFERENCES `Podcast` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=40527565 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Entry in slow query log:
# Time: 190613 0:00:02 # Query_time: 3.903592 Lock_time: 0.000017 Rows_sent: 1405 Rows_examined: 1405 SET timestamp=1560384002; select `guid` from `Episode` where `podcast_id` = 35011;
EXPLAIN for this query:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Episode ref Episode_podcast_id Episode_podcast_id 4 const 1405
The table has around 40 million rows. Is this just the time it's taking to retrieve 1405 values from this table? If so, how can I determine what the bottleneck is?
SELECT SQL_NO_CACHE guid from Episode where podcast_id = 35011 runs quite quickly from the console, it's taking no more than 0.01s, which confirms that it's pretty strange for it to ever take 3+ seconds.
The query returned a total of 85,255 characters across the 1405 rows (the guid is per episode, and this podcast has 1405 episodes.).
Interestingly, when I try the same query with different
podcast_idvalues I often get a multi-second response time, but even when using
SQL_NO_CACHE (including for the initial query) each subsequent query was near instant. No idea what's going on there. No specific timing that I can see, looks like it's fairly common throughout the day.
innodb_buffer_pool_size is 12101615616 (set by AWS RDS based on instance size).
The GUIDs are determined by the podcast publisher in this case so there is potential for quite large strings to be used and we can't risk truncating them. Looking at it now we should probably just be using TEXT, but either way it doesn't seem like the index is necessary to solve this issue.
Additional information as requested:
RDS instance has 16GB RAM
Parameters (RDS doesn't have an accessible my.cnf): https://pastebin.com/nhxHszqt
SHOW GLOBAL STATUS: https://pastebin.com/w6BPnR0e
SHOW GLOBAL VARIABLES: https://pastebin.com/wtQXDnyq
SHOW FULL PROCESSLIST: https://pastebin.com/rMBrf6pF
mySQL Tuner Output: https://pastebin.com/dC7ZJytW
SHOW ENGINE INNODB STATUS: https://pastebin.com/XVpA0RG3