MySQL Forums
Forum List  »  Performance

Re: Index suggestion needed for performance improvement
Posted by: Ronald Brown
Date: October 04, 2015 09:18PM

> 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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Index suggestion needed for performance improvement
926
October 04, 2015 09:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.