| Subject | Re: [firebird-support] more efficient filter |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-03-13T02:09:45Z |
At 09:29 AM 13/03/2009, you wrote:
However, since this kind of "control value" is often used in a way that causes a small number of possible values to be stored, it might degrade performance if you have this field indexed on its own. The trick in this case - should the optimizer decide that an indexed search would be faster than a non-indexed one - is to create a composite index consisting of (ThisColumn + PrimaryKey).
But there is no "rule". It very much depends on the geometry of the data at the time. Try your filter with and without the recommended index to test its effect with *your* data. (It's enough just to switch the index off, once created, using ALTER INDEX blah INACTIVE as table owner/sysdba).
If you find the index is more effective than no index then pay attention to maintaining its healthy shape from time to time, especially if you start to notice degradation in the performance of the queries that use it. (In fact, the above operation, followed by ALTER INDEX blah ACTIVE is one way to do that job.)
./hb
>Hello,Neither. It is purely a matter of taste.
>
>I've seen on different database designs two main filters approaches in order to select based on a "status" field: a status based on char(1) and status based on number.
>
>For example,
>
>1rst approach:
>SELECT * FROM table WHERE status = 'A'
>
>2do approach:
>SELECT * FROM table WHERE status = 1
>
>The question is: What approach is the best in order to improve query performance??
However, since this kind of "control value" is often used in a way that causes a small number of possible values to be stored, it might degrade performance if you have this field indexed on its own. The trick in this case - should the optimizer decide that an indexed search would be faster than a non-indexed one - is to create a composite index consisting of (ThisColumn + PrimaryKey).
But there is no "rule". It very much depends on the geometry of the data at the time. Try your filter with and without the recommended index to test its effect with *your* data. (It's enough just to switch the index off, once created, using ALTER INDEX blah INACTIVE as table owner/sysdba).
If you find the index is more effective than no index then pay attention to maintaining its healthy shape from time to time, especially if you start to notice degradation in the performance of the queries that use it. (In fact, the above operation, followed by ALTER INDEX blah ACTIVE is one way to do that job.)
./hb