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: 
```
CREATE VIEW dbo.vwEli
WITH SCHEMABINDING
AS
    SELECT userId, cnt = COUNT(*)
    FROM Eli
GO

CREATE CLUSTERED INDEX ix ON dbo.vwEli (userId)
GO

SELECT cnt
FROM dbo.vwEli --WITH(NOEXPAND)
WHERE userId = 'my_user'

```
The easy fix to see improvement would be add an index on the table on the userID, if you intend to use the userID filter very often in your queries. Of course that if userID is the primary key of your table, the index would not be necessary and maybe other ways to improve needs to be checked.
Now, the query you have provided should be applied as this to make it easier for you:

```
SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count] 
FROM sys.sysindexes i WITH (NOLOCK) 
WHERE i.indid in (0,1) 
AND OBJECT_NAME(i.id) = 'Eli';

```

The original query you posted listed the row counts for all objects in the database in descending order of the number of rows.