500000/100 = 5000 blocks
10ms per block to read
Conclusion: about 50s to do
COUNT(*). So, 40s is reasonable.
But why count the number of rows in the table? Is that really your most imporant query? Let's focus on them. Sometimes the simple addition of a composite index can greatly improve performance.
500K rows / 20GB is 'large', but not huge. We can probably help you make it more performant.
What is the value of
innodb_buffer_pool_size? How much RAM? If there had been enough cache space, the query might have run in 4s.
TEXT columns, when large, are stored "off record". This implies that reaching for them may take an extra disk hits. What are some of your other 'slow' queries?
Please provide the things already asked for in the Comments.
PRIMARY KEY (`posts_id`),
KEY `posts_type` (`posts_type`),
KEY `comment_count` (`comment_count`),
KEY `published_at` (`published_at`),
KEY `archive_at` (`archive_at`),
KEY `create_at` (`create_at`),
KEY `modified_at` (`modified_at`),
KEY `edition` (`edition`),
KEY `hot_posts` (`hot_posts`),
KEY `fk_writter_id` (`fk_writter_id`),
KEY `jamil_fk_page_id__published_at` (`fk_page_id`,`published_at`),
KEY `media_tags` (`media_tags`),
KEY `sort_order_all` (`sort_order_all`),
KEY `sort_order_featured` (`sort_order_featured`),
KEY `sort_order_hot` (`sort_order_hot`),
KEY `featured_2` (`featured`,`sort_order_featured`,`publish_status`,`posts_type`,`published_at`),
KEY `featured` (`featured`),
KEY `publish_status` (`publish_status`),
KEY `sort_order_page_featured` (`sort_order_page_featured`),
KEY `page_featured` (`page_featured`)
`published_at` datetime NOT NULL,
AND fk_page_id IN ( '1234' )
AND posts_type IN ( 'home' )
AND publish_status = 'published'
AND posts_id > '1083775'
AND published_at <= '2019-02-19 08:12'
ORDER BY published_at ASC
LIMIT 0, 1;
This index may help:
INDEX(publish_status, fk_page_id, posts_type, published_at)
The problem is that there are too many non-trivial things in the
WHERE clause. The Optimizer punted and said "Well, I will use the
published_at index in hopes of helping with
LIMIT; but I will cross my fingers and hope that I find a valid row (based on the other criteria) pretty soon." But, apparently, there was not such a row very soon.