sql server - SQL Optimize Group By Query -

i have table here following fields: id, name, kind. date

data:

id     name     kind   date     1      thomas   1      2015-01-01     2      thomas   1      2015-01-01     3      thomas   2      2014-01-01     4      kevin    2      2014-01-01     5      kevin    2      2014-01-01     5      kevin    2      2014-01-01     5      kevin    2      2014-01-01     6      sasha    1      2014-01-01 

i have sql statement this:

select name,kind,count(*) recordcount mytable  group kind, name  

i want know how many records there name , kind. expected results:

name     kind     count thomas   1        2 thomas   2        1 kevin    2        2 sasha    1        4 

the problem is big table, more 50 million records.

also i'd know result within last hour, last day, last week , on, need add where clause this:

select name,kind,count(*) recordcount       mytable  date > '2015-26-07' group kind, name  

i use t-sql sql server management studio. of relevant columns have non clustered index , primary key clustered index.

does have ideas how make faster?

update:

the execution plan says:

• select, compute scalar, stream aggregate, sort, parallelism: 0% costs.
• hash match (partial aggregate): 12%.
• clustered index scan: 88%

sorry, forgot check sql-statements.

50 million lot of rows
not can optimize query can see

possibly composite index on kind, name
or try name, kind
or name

i think query optimizer smart enough not factor but switch group name, kind name more unique

if kind not unique (just 1 , 2) may better off no index on

i defrag indexes have