MySQL query optimization

I want to add an index to improve performance of this  query.
From your experience, which index do you think I would benefit the most?  MyTable has several million rows.  There are maybe about 100 unique client_code, view, batch_timestamp combinations.

Please include your thoughts.

select   client_code
              , view
              , batch_timestamp
              , sum(if(file_reference = null,  1, 0)) as pending
              , count(*) as total
       from mytable
       where
              batch_timestamp >= '20110310'
             and batch_timestamp < DATE_ADD('20110310', INTERVAL 1 DAY)
       group by client_code, view, batch_timestamp
       order by batch_timestamp ;
rudy201Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mayankagarwalConnect With a Mentor Commented:
create a Nonclustered Index on  client_code, view, batch_timestamp.
0
 
arnoldCommented:
could you post the show create table mytable

batch_timestamp, client_code shold have an index.
0
 
mayankagarwalCommented:
create nonclustered index idx_mytable_idx1 on mytable(client_code, view, batch_timestamp)
0
 
rudy201Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.