Need SQL Optimization Help

select top 1 ur.type_id, sum(ur.rate) as rate from iwg_user_rating as ur
join iwg_media as m on m.id = ur.type_id
where m.user_id = 17
group by ur.type_id order by rate desc


Need to pull back top 1 record and this query is taking around 30 second with only 10 and 50 records in both the table. both the tables are indexed properly.

 

Select top 1 from (Select ur.type_id, sum(ur.rate) as rate from iwg_user_rating as ur
join iwg_media as m on m.id=ur.type_id
where m.user_id=17
group by ur.type_id 
order by rate desc)

this breaks it up a bit better, allowing the more efficient selection statement to run, and then the less efficient select Top 1 statement to only roll over the result set when it has been determined.

Code (markup):

 

You should be able to remove the group by clause. Since you're only selecting a single record, there's no reason to group anything. This is in reference to the original query.

 

Also, what are the data type of user_id column?