Index Searches / Sec high!

Hi,

I have a query and runs against a large table having half a million records. Can ne body tell me how to troubleshoot index searches/sec which is the highest when checked with performance counters ?

i am putting down my query if ne body could suggest.. way to write it or index the columns.

Query:
SELECT C.USERNAME, RIGHT(C.PLAN_TYPE, 2) AS PTYPE, C.TOTAL_SESSION, C.IPADDRESS AS IPADDRESS
FROM DBO.USERS_AUTHENTICATION C WHERE C.BACKUPACCTSTATUS = CASE C.PLAN_TYPE WHEN 6 THEN 1 ELSE 0 END
AND C.USERNAME ='xxx'

Thanks in advance.



RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

it will depend on the selectivity of the the columns
i am assuming USERNAME is reasonable selective
if only a few rows for a given USERNAME exists, then the minimum index is

CREATE INDEX IX_USERS_AUTHENTICATION ON USERS_AUTHENTICATION(USERNAME)

otherwise, if BACKUPACCTSTATUS increases the selectivity, following will do:
CREATE INDEX IX_USERS_AUTHENTICATION_1 ON USERS_AUTHENTICATION(USERNAME, BACKUPACCTSTATUS, PLAN_TYPE)

if there are still many rows that meet the criteria, then do a full covered index

CREATE INDEX IX_USERS_AUTHENTICATION_2 ON USERS_AUTHENTICATION(USERNAME, BACKUPACCTSTATUS, PLAN_TYPE, TOTAL_SESSION, IPADDRESS)

U can use Show Execution plan in Query Analyzer to find out the index analysis
and
Try to optimize the query by creating indexes on the columns which u were using
for Where clause in the query

and Run the execution plan and check the time taking for the query.





Rushendra