Index bit in MYSQL

Question

updated question:
Suppose that the data that interests me is only those for which the field value = 1, and the actual ratio of the data field 1 to 0 is very small (for example, 1%). in this case will index the usefulness of the field my select where field = 1 query?

original question:
I have an int field that will have a value of 0 or 1, will index this field to speed up the selection of queries, such as:

  select * from xxx where field = 1;

Answer 1

Generally speaking, no. The bi-state field does not speed up queries when indexing, because you should look at half the lines on average. You want your index entries to be selective — this entry in the index should only make up a small percentage of the possible values ​​(say, less than 10%, preferably a fraction of a percent). Then using the index ignores most of the data in the table, which gives you a performance advantage.

Some DBMSs support bitmap indexes. They may help, but you are still faced with the problem of selectivity.

---

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

Answer:

• For those queries where you indicate that the value is 1, yes, the index in the column can provide an advantage, provided that the optimizer actually uses the index. You may need to configure the DBMS to understand that the index is distorted in favor of using it with queries, where the value is 1; this tends to be specific to the DBMS, but updating statistics in different guises is the name of the game, possibly using hints in SQL queries. Of course, if the optimizer never uses the index, then it still does not give any benefit - and the optimizer may decide that other indexes somehow help it.

• For those queries where the value is 0, the index should not be used. Most likely, the DBMS will continue to maintain an index for values ​​of 0, even if it should never use them. It would be an unusual DBMS that could be instructed to "only index this column for values ​​other than zero," although this would be very useful.

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

Note also that a composite index for some other commonly used columns, and then for a bit field, may provide some benefit. Thus, if you almost always choose a date range, then the composite index of the date columns and bit fields (perhaps in that order) should provide you with a good index.

Answer 2

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

Answer 3

It is true that the values ​​are not unique, so it should check all the values ​​and you will not get any performance improvement from binary search.

However, there is still something to consider. If the database is large and cannot fit into memory, it must load data into memory before it can view it. If there is an index that contains data, it can be much faster to load only that index than the entire table. Probably depends on the number of columns in the table.