Re: Index suggestion needed for performance improvement
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