7

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!

2
  • 1
    Is this related to your other question that you recently opened?
    – JNK
    Aug 13, 2012 at 17:11
  • 1
    You might also not want to pass everything to the count function. Depending on the database that may not be optimal. It may grab everything, then count it. It might be easier to do just a count(message_type_id). Should give you the same result, but helps the engine to cut off extra rows it doesn't need to care about. Aug 15, 2012 at 12:47

4 Answers 4

6

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
7
  • I assume you mean SELECT message_id? (Borderline was going to edit this myself.)
    – Jon Seigel
    Aug 14, 2012 at 13:22
  • @JonSeigel: makes no difference unless message_id is NULLable
    – gbn
    Aug 14, 2012 at 13:40
  • Oops, sorry, I meant the SELECTs in the derived table.
    – Jon Seigel
    Aug 14, 2012 at 16:02
  • 1
    Right, but the UNION will discard duplicates, giving an overall COUNT of either 0 or 1, always. So either the SELECTed field needs to be unique for the rows, or the derived table needs UNION ALL instead. Sorry if I'm missing something obvious here.
    – Jon Seigel
    Aug 14, 2012 at 19:07
  • 2
    @JonSeigel: oh bloody hell yes. will correct
    – gbn
    Aug 14, 2012 at 21:01
4

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.

4

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.

2
  • Thanks for the suggestion but it did nothing for the query time
    – Darthtong
    Aug 14, 2012 at 16:28
  • And you were right, the filtered index wasn't used by the optimizer
    – Darthtong
    Aug 14, 2012 at 16:47
3

A filtered index might help:

CREATE INDEX f_idx_1 
  ON dbo.messages(message_type_id, message_id, hidden) 
  WHERE message_type_id = 1;
4
  • It might or might not help: I've been burned a few times by the optimizer that ignores filtered indexes.
    – A-K
    Aug 13, 2012 at 17:43
  • @AlexKuznetsov yep, I said might for a reason. Aug 13, 2012 at 17:44
  • That said, I have found them to be relatively more reliable on modern builds than when the feature was first introduced. Aug 13, 2012 at 17:48
  • IIRC, SQL Server uses filtered indexes better if the filter to by is in the column list. So CREATE INDEX f_idx_1 ON dbo.messages( message_type_id, message_id, hidden) WHERE message_type_id = 1. Yeah, some good stuff here on that technet.microsoft.com/en-us/library/cc280372.aspx#Restrictions
    – gbn
    Aug 14, 2012 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.