index a bit field in MYSQL

updated question:

suppose the data I'm interested in is only those with field=1 and the actual ratio of data wheere field is 1 vs 0 is very small(eg. 1%) in this case, would index the field benefit my select where field =1 query?

original question:

I have a int field that wil开发者_StackOverflowl have either 0 or 1 value, would indexing this field speed up select queries such as:

select * from xxx where field=1;

Generally speaking, no. A bi-state field doesn't speed up queries when indexed because you have to look at half the rows on average. You want your index entries to be selective - a given entry in the index should represent only a small percentage of the possible values (say, less than 10%, preferably into the fractions of a percent). Then using the index ignores most of the data in the table, which is what gives you a performance benefit.

Some DBMS support bitmap indexes. They can help, but you still run into the problem of selectivity.

The updated question says that the number of values with value 1 will be small (less than one percent); will an index give you a benefit now?

The answer is:

• For those queries where you specify that the value is 1, then yes, an index on the column could provide a benefit, provided that the optimizer actually makes use of the index. You may need to tweak the DBMS to make it realize that the index is skewed in favour of using it with queries where the value is 1; this tends to be DBMS-specific, but updating statistics in various guises is the name of the game, possibly using hints in the SQL queries too. Of course, if the optimizer never uses the index, then it still provides no benefit - and the optimizer may decide that other indexes help it more in some way.

• For those queries where the value is 0, the index should not be used. The chances are, though, the the DBMS will continue to maintain the index for the 0 values too - even though it should never use them. It would be an unusual DBMS that could be commanded 'only index this column for the values other than zero', even though that would be very beneficial.

So - it depends. It depends on the queries, and it depends on the optimizer.

Note too that a composite index - on some other customarily used columns and then the bit-field may well provide some benefit. So, if you almost always select on a date range, then a composite index on the date and bit-field columns (probably in that order) should provide you with a good index.

Yes. But you may not want to take the consequent performance hit for updates for such a small field; if your rows are 50/50 0 or 1, a full table scan may still be reasonable.

It's true that the values are not unique so it has to scan over all the values and you do not get any performance improvement from binary searching.

However, there is something else to consider. If the database is large and does not fit in memory then it must load the data into memory before it can look through it. If there is an index that contains the data it may be much quicker to load just this index than the entire table. Probably depends on how many columns the table has.