Optimizing select count(*) from table

Question

I have table Eli with 1 million records. When I query the following:

Select count(*) from Eli where userId ='my_user'

It is taking more than 10 mins to give results. I searched web and found better way to optimize the query from http://dbatipster.blogspot.com/2009/08/get-row-counts-fast.html.

How do I utilize the the following query into my above query-

SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count] 
FROM sys.sysindexes i WITH (NOLOCK) 
WHERE i.indid in (0,1) 
ORDER BY i.rowcnt desc

Answer 1

Without touching on properly building a table, I would use something like this:

SELECT COUNT(userID) FROM Eli (NOLOCK)
WHERE userId ='my_user'

The (NOLOCK) hint allows you to select from the table without other transactions against the Eli table being committed, meaning you're not waiting for other updates and inserts to complete before returning your results.