Re: Utilize maximum system resources for better performance

[Rick James]

Use utf8 if you need it; don't use it you don't. In particular, you _probably_ should not use it for `hash`. CHAR(40) in utf8 always takes 120 bytes; in latin1 or ascii, it takes only 40 bytes.

Do you have other tables that JOIN to this one via `record_num`?

Is the 6.5M rows a complete replacement? (In which case, I will give one recommendation.)
Or is it some new, some changed data? (In which case, another.)
Are you using LOAD DATA? INSERT one row at a time? Other?

The Query Cache is useful only for relatively static tables. Perhaps this is static except for once a day?

SELECT count(*) as total_results FROM content WHERE content.enabled = 1 ;
requires scanning part of one of the indexes, as you can see from EXPLAIN (e_v).

Oh, pagination. My blog on that covers many of your problems:
http://mysql.rjweb.org/doc.php/pagination
WHERE enabled = 1 ORDER BY upload_date DESC LIMIT 0,25
is an excellent candidate for what my blog suggests.

There are over 6M 'enabled' records. Simply recompute that daily, then format the response as "> 6 million", not "exactly 6,686,811".

SELECT count(*) as total_results
    FROM content
    WHERE content.enabled = 1
      AND category BETWEEN 400 AND 420
      AND upload_date >= '2014-08-08'
      AND verified = 1
[/c0de]
can be sped up slightly by putting the "= const" fields first.  That is:
INDEX(enabled, verified, category, upload_date)
(The first two columns can be in either order.  It's unclear what the better order for the last two is.)
If you turn the category into an IN, _and_ your version has MRR, you _might_ get more speed.

See my indexing cookbook:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Queries 1,2,3 from pt-query-digest smell like manually run things, not production queries.  Some involve reading 6M rows, which seems unlikely.