MySQL – Should you put an index on a Boolean field to help query performance?
I am often asked if it makes sense to place an index on a Boolean field in order to improve query performance. In general, because a boolean value can only have three values (True, False, Null), this low cardinality would suggest that adding an index will not help performance, as the query optimizer will still usually perform a table-scan if you have an even distribution of values within your DB.
One situation in which an index on a boolean field (or other low cardinality field) might be useful is if there are relatively few of one of the values, for example 5 True values in a table of millions of records and you are searching for those few values on a regular basis.
However, you might index a boolean value on a combination of fields. Indexing on a single Boolean might be pointless, because there’s only 2 (or 3) values. However, indexing on 16 boolean values has the potential of 2^16 values. It might help to make a combined index but you should understand how the combined index can and cannot be used and be aware that the order of the columns matters.
In general, you should always profile your system to see if there are queries that are too slow and consider adding another index to handle those queries. Sometimes a single combined index can be used for multiple queries and others time you will need to make an index for each type of query. Remember that adding indexes slows down modifications to the data so it is possible to have too many indexes. There is always a trade-off when creating multiple indexes.
While this post is interesting, the fact that you’re staying on the surface of things and not actually giving any real information slightly bothers me.
It’s great to tell us that we should understand how combined indexes can and cannot be used and tell us that the column of orders matter, but not giving any explanation on this or links to documentation where we can find this information makes this article fairly useless
Donald, I am sorry that you feel that way. Without a specific example, it is hard to provide a more detailed answers. My main point is that indexing a single boolean field is almost never useful. However, if you have multiple boolean fields, and your search will use a combination, then a covered index can significantly improve query performance.
If you have a specific issue you are trying to resolve, send me an email and I will try to respond to you directly.