Hi Colin!

What's the plan reported? Assuming your foreign key field is evenly
distributed, a plan like
THETABLE (INDEX FK_TYPE, FK_TYPE, FK_TYPE, FK_TYPE) should execute much
faster than one minute. What you should avoid is indexes for Status and
InUse, since I assume this to be a lot less selective than type.

Until proven otherwise, I disagree with Alan who advices you to stay away
from using IN. Generally, using IN (<subselect>) does have negative
influence on performance (or at least had until Arno appeared), but I
haven't heard that IN (<constants>) have the same negative effect.

HTH,
Set

At 18:12 26.06.2003 +1200, you wrote:

>Hi all,
>
>We are running a simple query against a table which is taking to long to
>execute... Details are as follows:
>
>Table has about 2 million records which is growing all the time (system
>been running for 8 months), if anything, the growth rate will increase.
>The table has about 40 fields of time stamps, integers, floats, a few
>var chars and some blobs for any large text fields.
>
>When we run a query such as
> Select *
> from TheTable
> where TheTable.Type in (342, 234, 6372, 234)
> and TheTable.Status = 5
> and InUse = 'T'
>
>The type field is an integer foreign key with perhaps 20000 different
>values, The Status field is a small int with about 10 different values
>and InUse is a Char(1) field with two values (T and F).
>
>This query would typically return between 50 and 500 rows. We get a
>response time of > 1 minute. Is this normal for a 2 million record
>table, I believe it is using the foreign key index in the query (it
>mentions it in the plan).
>
>Any suggestions on how I should refine my query, indexes, table
>structure. The last system used two tables to store the same data... One
>table held data that was at a status < finished which is where most of
>the queries were run. Once the data was 'finished' it was moved to the
>larger table holding all the old data. I was hoping to get away from
>this as there would be a bit more involved in behind the scenes in the
>moving of data between the two tables. The performance figures might
>suggest that we need to make the move to two tables.
>
>Any suggestions appreciated.
>
>Regards
>C Fraser
>
>
>
>######################################################################
>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.
>######################################################################
>
>
>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/