Speeding up COUNT(*) - WHERE clause slowing query

Question

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on 'messages_id'. The 'hidden' field is a bit field (not null) and 'message_type_id' is an int field (not null) and message_id is an int field (PK).

SELECT COUNT(*)
FROM messages
WHERE message_type_id = 1
AND (hidden = 0 OR message_id = @message_id)

Thanks in advance!

Answer 1

Another approach to remove the OR.
You could try this and have 2 indexes on the table to satisfy the WHERE clauses.

SELECT
    COUNT(*)
FROM
    (
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND hidden = 0
    UNION
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND message_id = @message_id
    ) X

Answer 2

Because of the OR predicate, the best you'll be able to do for this query is get an index scan plan. I'm assuming you're seeing a table scan right now. Depending on how wide the base table is, this may or may not make a big difference in the execution time.

Assuming the primary key index is clustered, create an index on message_type_id with the hidden column INCLUDEd. (The primary key column as the clustering key will be included in the index automatically.)

If you're only ever searching the table for message_type_id = 1, you could filter the index to only capture those rows instead of all the table rows.

Answer 3

You can create a covering index on the following columns (message_type_id, hidden, message_id). Try out different order of columns in the index: that might make some difference.

I would rather not use a filtered index: it is quite likely that the optimizer would fail to use it.

Answer 4

A filtered index might help:

CREATE INDEX f_idx_1 
  ON dbo.messages(message_type_id, message_id, hidden) 
  WHERE message_type_id = 1;