> key: e_c_d_v -- Note which index it used
> key_len: 3 -- enabled is 1 byte + category is 2; so it is using both `enabled` and `category` but not the other fields.
> rows: 11508 -- The _estimated_ number of rows to read.
> Rows_examined: 1525431 -- The _actual_ number of rows with that `enabled` and `category`.
How much RAM do you have? What is the value of innodb_buffer_pool_size? That setting should typically be 70% of RAM.
1525431 rows is a lot. Since you managed to read all of them in 2.2 seconds, I suspect they were all cached in the buffer_pool (and the buffer_pool is big enough).
There is _no_ index that will prevent reading 1525431 rows, building a tmp table, sorting it, and finally delivering what LIMIT says.
> this issue fixed after i created index enabled_seeders_upload_date
Did the EXPLAIN change? I suspect is is very similar. If it is different, I would be excited to see it.
> where can i learn how to create indexes ?
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
(It does not cover everything, but I think it is a big starting point.)
It effectively says INDEX(enabled, category) is the best. You have at least 2 indexes that _start_ with those fields, so they are virtually as good. (Hence, I did not push for adding this one.)
Here is a technique that _might_ speed it up _some_:
SELECT content.title,content.og_name,content.record_num,content.category,
content.upload_date,content.hash,content.comments_count,
content.verified,content.uploader,content.size,content.seeders,
content.leechers
FROM content
JOIN
( SELECT record_num
FROM content
WHERE enabled = 1
AND category IN (800,801,802,803,804,805,806,807,808,809,
810,811,812,813,814,815,816,817,818,819,820)
ORDER BY seeders DESC , upload_date DESC
LIMIT 0,25
) x ON content.record_num = x.record_num;
# together with this "covering" index for the subquery:
INDEX(enabled, category, seeders, upload_date, record_num)
Why it _might_ help:
1. The subquery will rummage through 1525431 _narrow_ rows in the _covering_ index.
2. It will create a narrow tmp table for sorting and extracting 25 record_nums.
3. The outer SELECT will look up only the 25 records.
Caveat: You probably need ORDER BY seeders DESC , upload_date DESC at the end to get the rows in the right order. But this is a very minor filesort (25 rows, versus 1525431).
The principles behind this 'trick' are:
1. "If FROM ( SELECT... ) will significantly decrease the number of rows looked at, the overall query may run faster."
2. "A covering index is faster."
The caveat comes in that the added complexity costs something.
Another technique that _might_ help. Or it might make things worse:
INDEX(enabled, seeders, upload_date)
This would be beneficial only if those categories happened to be near the end of the seeders.