> How much RAM do you have? What is the value of
> innodb_buffer_pool_size? That setting should
> typically be 70% of RAM.
i have 32 GB RAM , its a dedicated server acts as web-server and mysql server.
current
innodb_buffer_pool_size = 15G
> Did the EXPLAIN change? I suspect is is very
> similar. If it is different, I would be excited
> to see it.
explain changed key from e_c_d_v(enabled_category_upload_datverified) to e_s_d (enabled_seeders_upload_date)
mysql> explain 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 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\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: content
type: range
possible_keys: e_c_d_v,e_d_v,e_c_v,e_v,e_u,e_s_d
key: e_s_d
key_len: 1
ref: NULL
rows: 3398901
Extra: Using index condition; Using where
1 row in set (0.00 sec)
> Here is a technique that _might_ speed it up
> _some_:
>
>
> SELECT
> content.title,content.og_name,content.record_num,c
> ontent.category,
>
> content.upload_date,content.hash,content.comments_
> count,
>
> content.verified,content.uploader,content.size,con
> tent.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.
This looks great.
> 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.
this index is what i created , and then queres are fast.
Thanks