• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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 ;
0
rudy201
Asked:
rudy201
  • 2
1 Solution
 
arnoldCommented:
could you post the show create table mytable

batch_timestamp, client_code shold have an index.
0
 
mayankagarwalCommented:
create a Nonclustered Index on  client_code, view, batch_timestamp.
0
 
mayankagarwalCommented:
create nonclustered index idx_mytable_idx1 on mytable(client_code, view, batch_timestamp)
0
 
rudy201Author Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now