ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
You mentioned a problem with creating an INDEX, but I see several other problems.  Many of them, if fixed, could avoid the index problem.

Huge OFFSET


  LIMIT 100 OFFSET 214000


This sounds like a crawler is scraping your site.  Block the crawler.

Huge number of "pages"

If it is not a crawler, then please explain who would would have the patience t0 step through to page 2141 !!

More than that, why do you have that many pages??  That is not what WP is designed for.

Can't fully index

Because of this "range", it is not possible to build an truly efficient index for the query:

AND wp_posts.post_date != '0000-00-00 00:00:00'


Do you really have 'zero' dates in your table?

Engine

MyISAM is deprecated and going away; switch to InnoDB.  InnoDB has different limitations on indexes, so it may make your index problem go away.

1000

Do you really have long passwords?  SELECT MAX(CHAR_LENGTH(post_password)) FROM wp_posts;  If it is significantly less than 255, shrink the column definition.

Do you need utf8mb4 for the password?  Is it a "plaintext" password; if so, then that charset is probably valid.  If it has been hashed, consider ascii, which won't have the 4x issue.