Ugh: 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.
Other notes:
WHERE type=... ORDER BY rating
can 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 = 256M
andmax_heap_table_size = 256M
are dangerously high for a tiny 2GB of RAM. Shrink them to only 1% of RAM.Instead of
( SELECT COUNT(*) FROM...) >=1
, doEXISTS ( SELECT 1 FROM ...)