Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Is this related to your other question that you recently opened? –  JNK Aug 13 '12 at 17:11
    
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. –  Chris Aldrich Aug 15 '12 at 12:47

4 Answers 4

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
share|improve this answer
    
I assume you mean SELECT message_id? (Borderline was going to edit this myself.) –  Jon Seigel Aug 14 '12 at 13:22
    
@JonSeigel: makes no difference unless message_id is NULLable –  gbn Aug 14 '12 at 13:40
    
Oops, sorry, I meant the SELECTs in the derived table. –  Jon Seigel Aug 14 '12 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 '12 at 19:07
2  
@JonSeigel: oh bloody hell yes. will correct –  gbn Aug 14 '12 at 21:01

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.

share|improve this answer

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.

share|improve this answer
    
Thanks for the suggestion but it did nothing for the query time –  Darthtong Aug 14 '12 at 16:28
    
And you were right, the filtered index wasn't used by the optimizer –  Darthtong Aug 14 '12 at 16:47

A filtered index might help:

CREATE INDEX f_idx_1 ON dbo.messages(message_id, hidden) 
WHERE message_type_id = 1;
share|improve this answer
    
It might or might not help: I've been burned a few times by the optimizer that ignores filtered indexes. –  AlexKuznetsov Aug 13 '12 at 17:43
    
@AlexKuznetsov yep, I said might for a reason. –  Aaron Bertrand Aug 13 '12 at 17:44
    
That said, I have found them to be relatively more reliable on modern builds than when the feature was first introduced. –  Aaron Bertrand Aug 13 '12 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 '12 at 7:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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