| Subject | Re: [firebird-support] indexing a status column |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-07-27T03:45:37Z |
At 03:23 AM 27/07/2004 +0700, you wrote:
generally a sign that it's the right thing to do. I'd still want to test
retrieval speed against the natural scan, though. It might be that other
criteria in the search provide sufficient limitation that a natural scan of
the already limited stream would be faster. You don't need to drop the
comp. index to test the natural scan - just add an OR to the criterion, i.e.
where....
{other criteria}
and status = 1 or 1 = 1
relational design (or simply forgive him/her for that sudden rush of blood
to the head!!)
/heLen
>Hello...,No.
>
>Background: It has been established that indexing a boolean (yes/no,
>1/0) column is not recommended in Firebird for performance reasons (i.e.
>update/insert speed, confusing the optimizer).
>
>My situation: I have a large table with a 'status' column. This table
>has an INTEGER primary key fed from a generator. The 'status' column is
>also an integer field, but is limited to five values ([1..5]) at the
>moment. The distribution of these five values are not even, with the
>value '1' expected in more than 60% of the rows. The rest of the values
>are divided more or less evenly.
>
>At some point in the near future i'll need to write some
>queries/storedproc that will only process records of a certain 'status'
>value. Without an index, this operation will normally require a full
>table scan. This makes me a bit nervous.
>
>Will it be a good idea to either :
>a. create an index on the 'status' column ?
>b. create a compound index on 'status' + 'pk' columns ? (supposedly thisYes. If the optimizer prefers the index to the table scan, then it's
>will remove the 'long index chain' caused by [a] above. In a cursory
>test with a small dataset, "select * from table where status=1" does
>indeed use the index.). Is this the best thing to do?
generally a sign that it's the right thing to do. I'd still want to test
retrieval speed against the natural scan, though. It might be that other
criteria in the search provide sufficient limitation that a natural scan of
the already limited stream would be faster. You don't need to drop the
comp. index to test the natural scan - just add an OR to the criterion, i.e.
where....
{other criteria}
and status = 1 or 1 = 1
>c. split the table into five different tables with identical structure,No!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
>and remove the 'status' column altogether ?
>(I don't like the idea atIf that "someone" is someone you care about, buy him/her a good book on
>all, but someone suggested this, so i'll ask for second opinions).
relational design (or simply forgive him/her for that sudden rush of blood
to the head!!)
/heLen