Should I use indexing on these type of attributes

One certain tables I have an integer 'IsDeleted' field which defaults to 0.

This开发者_如何学运维 field basically flags a row as begin deleted or not. Now when querying I would specify to fetch records based on whether or not this flag has been set.

Would it be worthwhile to set an index on this attribute?

---

If there will be much more deleted records than live (or vise versa), then querying for the least used type will benefit from the index.

Say, if 1% of the records are deleted and you are querying for

SELECT  *
FROM    records
WHERE   isDeleted = 1

, the optimizer will choose the index, while for this query:

SELECT  *
FROM    records
WHERE   isDeleted = 0

it will use a full table scan.

If the number of neither deleted not retained records is below the tipping point (which depends on the record size and other things but as a rule of thumb can be considered 10%), then the index will be probably never used.

---

If you are working with MySQL you should use BIT datatype for this kind of operations regarding boolean checking and not integer. (see http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html).

True and False correspond to 1 an 0 respectively.

For those queries where you specify that the value is 1, an index on the column could be worthy, for 0, no. Details here