tsql query and index question

I have a table that stores photo information with id as Primary key:

id(PK), title, album_id, posted_by, published, filename, tags, ratings, date_posted

This table will hold infor of 100+ Million photos and I need to run this query like these frequently:

1) get all photos (just id,filename,title columns) of a given album

select id, filename, title from photos where album_id = @AlbumId and published = 1

2) get all published photos of a given user but exclude photos of currently viewing album

select id, filename, title from photos where posted_by='bob' and album_id <>10 and published = 1

I want to avoid index and table scanning. I need to use seek(say 100%) as much as possible.

Can this be done? What type of index and on which columns would help me achieve this?

Thanks


Bookmark and Share   Read more Read more...   Source: Stack Overflow