limit 100 offset 86500
Don't do pagination that way. Instead "remember where you left off". This has the drawback of no providing "jump to page 864", but who does that. And who does Next-next-next... 865 times?
Further discussion about this common problem: http://mysql.rjweb.org/doc.php/pagination
And it discusses the "left off" solution in more detail.
Another potential problem:
order by tmdb_rating desc -- Perhaps multiple titles have the same "rating"? If so, in what order do you want the titles listed? The simple answer is to specify some unambiguous (but somewhat arbitrary) order:
order by tmdb_rating desc, id desc.
Remembering where you left off in a compound order-by is more complex, but possible.
WHERE type=... ORDER BY ratingcan benefit from the "composite"
INDEX(type, rating), in this order.
3 unique keys sounds wrong.
2 TEXT columns being fetched hurts the performance. Don't do
SELECT *unless you really need all the columns.
tmp_table_size = 256Mand
max_heap_table_size = 256Mare dangerously high for a tiny 2GB of RAM. Shrink them to only 1% of RAM.
( SELECT COUNT(*) FROM...) >=1, do
EXISTS ( SELECT 1 FROM ...)