Skip to main content
Database Administrators

Return to Question

edited tags
Link
Aaron Bertrand
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619
Removed tag from title (we know it's SQL Server).
Source Link
Aaron Bertrand
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

SQL Server 2008 INNER JOIN making COUNT(*) slow

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_idmessage_id and user_iduser_id) and a foreign key setup connecting messages.user_idmessages.user_id and users.user_idusers.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?

SQL Server 2008 INNER JOIN making COUNT(*) slow

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?

INNER JOIN making COUNT(*) slow

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?

Post Migrated Here from stackoverflow.com (revisions)
Source Link
Darthtong
Darthtong
  • 263
  • 1
  • 3
  • 4

SQL Server 2008 INNER JOIN making COUNT(*) slow

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?