Sorry, the plans are as follows:

For the statement below (which has the manual index added, and is the
fastest by far)

WHERE (
(
(BigTable.Status = 0)
OR (BigTable.Status = 1)
OR (BigTable.Status = 2)
OR (BigTable.Status = 3)
OR (BigTable.Status = 4)
OR (BigTable.Status = 5)
OR (BigTable.Status = 6)
OR (BigTable.Status = 7)
)
AND
(SmallTable.NonIndexedField <> 0)
)

PLAN JOIN (SmallTable NATURAL,BigTable INDEX
(FK2_BigTable,BigTable_IDX2,BigTable_IDX2,BigTable_IDX2,BigTable_IDX2,Bi
gTable_IDX2,BigTable_IDX2,BigTable_IDX2,BigTable_IDX2))

Where FK2_BigTable is the index to the SmallTable and BigTable_IDX2 is
the index I created on the Status field (which I thought was bad because
it only contains a few values).

For the where statement

WHERE (
(BigTable.Status < 8)
AND (SmallTable.NonIndexedField <> 0 )

PLAN JOIN (SmallTable NATURAL,BigTable INDEX (BigTable_IDX1))

Where BigTable_IDX1 is another index that I created which is on the
Status and the BigTable.Type_Id field which is the foreign key to
SmallTable. I thought this was the preferred way of adding an index on a
column that contains lots of duplicate data.

Hope this provides some more light on the subject.

Regards
Colin


-----Original Message-----
From: Svein Erling [mailto:svein.erling.tysvaer@...]
Sent: Wednesday, 26 November 2003 9:50 p.m.
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Query tuning help... index with only a
few values does wonders... Why?


Hi Colin,
you forgot to tell us one essential part - the chosen plan.

--- In firebird-support@yahoogroups.com, "Colin Fraser" wrote:
> 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?

Well, to create such an index would definitely slow down updates and
deletes, though having an index on BigTable(Status, ID) - note that I
used ID and not Type_ID - could be useful for this query if the vast
majority of records contained status >=8.

Unless this is the case, I would simply recommend you to drop any
index for the status field altogether, though if other queries depend
on it to find rare statuses, then you could tweak your query by
changing your where clause to (BigTable.Status < 8 or 2=0).

Though I am still qurious about your plans...

HTH,
Set



To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/



######################################################################
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.
######################################################################