MySQL Forums
Forum List  »  Performance

Re: Index suggestion needed for performance improvement
Posted by: Ronald Brown
Date: October 03, 2015 02:10AM

Ronald Brown Wrote:
-------------------------------------------------------
> Here is another query , it was perfromaing faster
> previously but now its taking 2+ seconds.,
>
> # Query_time: 2.187370 Lock_time: 0.000112
> Rows_sent: 25 Rows_examined: 1525431
> SET timestamp=1443853148;
> 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,8
> 12,813,814,815,816,817,818,819,820) ORDER BY
> seeders DESC , upload_date DESC LIMIT 0,25;
>
> mysql> explain SELECT
> content.title,content.og_name,content.record_num,c
> ontent.category,content.upload_date,content.hash,c
> ontent.comments_count,content.verified,content.upl
> oader,content.size,content.seeders,content.leecher
> s FROM content WHERE enabled = 1 AND category IN
> (800,801,802,803,804,805,806,807,808,809,810,811,8
> 12,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,enabled_upload_date_se
> eders,enabled_upload_date_leechers,enabled_categor
> y_seeders_upload_date
> key: e_c_d_v
> key_len: 3
> ref: NULL
> rows: 11508
> Extra: Using index condition; Using
> filesort
> 1 row in set (0.00 sec)
>
>
> I added
> enabled_upload_date_seeders
> enabled_upload_date_leechers
> enabled_category_seeders_upload_date
> indexes, but still query is slow ., any idea ?


this issue fixed after i created index enabled_seeders_upload_date
where can i learn how to create indexes ? so i dont have to ask for help for each and everry qyery i find in slow quyery log ?

also is there any drawbacks if i create too many indexes ?

thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Index suggestion needed for performance improvement
832
October 03, 2015 02:10AM


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.