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 (
user_id) and a foreign key setup connecting
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?