Re: Optimize Select on update column

Eye-Gee.Kua_at_shell.com (egkua) wrote in message news:<46a2c097.0402121715.390e1cf2_at_posting.google.com>...
> Tony,
>
> There will be thousands of records where status = 'N'.

"Thousands" out of a "gig" of records is a very LOW number - suggests an index to me.

> Indexing on update column will give bad performance.

You keep saying this, but I don't know quite what you mean by it. Do you mean that UPDATES will be a bit slower because they have to update the index? If so, then that is undeniably true but hardly a serious reason to NEVER index such a column! A sense of proportion is in order...

> If there are gig on the non_unique_key_code column, would you think
> that it is a good idea to have it indexed on the two columns?

It depends, of course. If there are 1 billion rows in the table (is that what you mean by "gig on the column"?), and 1 million different non_unique_key values (avg 1000 rows per non_unique_key value), then an index on non_unique_key is likely to be helpful. If further to that, out of each 1000 rows typically 950 have status = 'Y' and only 50 have status = 'N', then adding status to the index will make it even more useful - you will read 50 records instead of 1000. On the other hand, if there are only a small number of distinct non_unique_key_values, then the index may be of no help at all.

Experiment! Received on Fri Feb 13 2004 - 04:19:49 CST