Range query is not using indexes in mysql
<p>I am trying to optimize the query which we are using to generate reports.
I think I did quite good to optimize to some extends.
Below was the original query:</p>
<pre><code>select trat.asset_name as group_name,trat.name as sub_group_name,
       trat.asset_id as group_id,
       if(trat.cause_task_type='AccessRequest',true,false) as is_request_task,
       '' as grouped_on,
       concat(trat.asset_name,' - {0} (',count(*),')') as table_heading
    from t_remote_agent_tasks trat
    where trat.status in ('completed','failedredundant')
      and trat.name not in ('collect-data','update-conn-params')
    group by trat.asset_name, trat.name
    order by field(trat.name,'create-account','change-attr',
                             'add-member-to-group',
                             'grant-access','disable-account','revoke-access',
                             'remove-member-from-group','update-license')  
</code></pre>
<p>When I see the execution plain in Extra column it says using where,Using Temporary,filesort.</p>
<p>So I optimize the query like this</p>
<pre><code>select  trat.asset_name as group_name,trat.name as sub_group_name,
        trat.asset_id as group_id,
        if(trat.cause_task_type='AccessRequest',true,false) as is_request_task,
        '' as grouped_on,
        concat(trat.asset_name,' - {0} (',count(*),')') as table_heading
    from t_remote_agent_tasks trat
    where trat.status in ('completed','failedredundant')
      and trat.name not in ('collect-data','update-conn-params')
    group by trat.asset_name,trat.name
    order by null
</code></pre>
<p>Which gives me the execution plan as using where,using temporary. So filesort is no more use and there is no extra overhead as optimizer doesn't have to sort,which will be taken care during group by.</p>
<p>I again went forward and created indexes on group by columns in same order as they mentioned in group by(this is important or optimization won't happen) i.e create index on (trat.asset_name,trat.name).
Now this optimization gives me Using where only in extra column. Also the query execution time got deduced by almost half(earlier it was 0.568 sec. and now 0.345sec ,not exact though it vary every time but more or less in this range).</p>
<p>Now I want to optimize the range query ,below part of query</p>
<pre><code>    trat.status in ('completed','failedredundant')
and trat.name not in ('collect-data','update-conn-params')
</code></pre>
<p>I am reading on <a href="http://dev.mysql.com/doc/refman/5.7/en/range-optimization.html" rel="nofollow noreferrer">mysl reference guide to optimize range query</a>,Which says not in is not in range query ,So I did the modification in query like this</p>
<pre><code>    trat.status in ('completed','failedredundant')
and trat.name in ('add-member-to-group','change-attr','create-account',
                  'disable-account','grant-access', 'remove-member-from-group',
                  'update-license')
</code></pre>
<p>But it doesn't show any improvement in Extra(I mean using index should be there,it is still showing using where).
I also tried by splitting both range part into unions(that will change the query result but still no improvement in execution plan)</p>
<p>I want some help on how to optimize this query more,mostly the range part(in part).
Any other optimization if I need to make on this?</p>
<p>I appreciate your time,Thanks in advance</p>
<h1>EDIT 1 I forgot to mentioned that I have index on trat.status also,So Below are the indexes</h1>
<ol>
<li>(trat.asset_name,trat.name)</li>
<li>(trat.status)</li>
</ol>