Hi all,

I have been trying to speed up a query from 10's of minutes into
something more usable.

We have managed to do it by adding in an index on BigTable (described
below) on the Status field, but there are only 8 or 9 values of status,
so I thought this was a no-no. To further explain...

We have 2 tables, say BigTable and SmallTable. SmallTable has a couple
of thousand rows, BigTable has millions of rows.

SELECT
BigTable.Id,
BigTable.Type_Id,
BigTable.Status,
BigTable.OtherInfo,
SmallTable.Code,
SmallTable.Name
FROM BigTable
INNER JOIN SmallTable ON (BigTable.Type_ID = SmallTable.ID)
WHERE (
(BigTable.Status < 8)
AND (SmallTable.NonIndexedField <> 0 )
)

Indexes we have are BigTable.Id, BigTable.Type_Id, SmallTable.Id (all
primary/foreign keys), and we have a manual index on BigTable.Status,
BigTable.Type_Id combined. I did this because I thought you were not
supposed to have an index on BigTable.Status because it only had 8 or 9
different values.

We changed (BigTable.Status < 8) to (BigTable.Status = 8) and it flies
(it used the previously mentioned manual index). We then changed it to a
series of OR statements (to mimic the < 8) and it crawls (no longer uses
the manual index).

BUT... If we have an index on Status itself, then it flies again, even
with all the ORs... So, performance wise, this works, but I just wonder
why it works so well when I thought you were not supposed do create such
an index.

So, should we keep the index??? The statistics on that index was
0.10000??somthing. I am not sure how to read those statistics, but it
did stand out from the other indexes which had statistics a lot smaller.

Why is it bad to create such an index when it seems to speed up the
query so much?

Regards
Colin


######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################