INNER JOIN making COUNT(*) slow

Question

I have a very simple query:

SELECT COUNT(*)
FROM messages
INNER JOIN users ON messages.user_id = users.user_id

With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to me). Test carried out on the messages table containing 1,000,000+ rows.

I have I have a primary key set on both tables (message_id and user_id) and a foreign key setup connecting messages.user_id and users.user_id.

The reason for this query is to provide the total number of records for a paging system.

What else can I do to speed up the query?

Answer 1

If the foreign key is not nullable, then there is no reason for the join at all, is there? So why not let SQL Server scan the most efficient index available instead of computing a join that will result in the exact same count?

SELECT COUNT(*) FROM dbo.messages;

That said, you can do this much more efficiently:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.messages')
  AND index_id IN (0,1);

This will be slightly inaccurate due to in-flight transactions, but it also won't block (or be blocked by) any activity against the table, so pick your poison.

Answer 2

You should create an index. An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX index_name
ON table_name (column_name)

You should only create indexes on columns (and tables) that will be frequently searched against.

Answer 3

You're counting different things, from more data sources. Of course it's going to be slower.