3

I have the following column:

is_owned_by_admin TINYINT(1) NOT NULL DEFAULT 0;

If I am querying this field a lot, do I need to add an INDEX to it, or is the boolean field just as 'fast' as an indexed boolean field? What is the difference?

David542
  • 104,438
  • 178
  • 489
  • 842
  • As with *all* indices, it depends on the multiplicity as it is applied to the query and suitability of use (and selection of such by the query planner) as an index. Being a boolean/bit is itself is irrelevant to the core question. The downside of unnecessary indices is increased storage and internal insert/update maintenance costs (and possibly confusing basic query planners) with no relevant gain. – user2864740 Nov 06 '14 at 23:39
  • http://stackoverflow.com/questions/10524651/is-there-any-performance-gain-in-indexing-a-boolean-field – vch Nov 06 '14 at 23:39

2 Answers2

4

There's not enough information in the question to give a definitive answer.

The most general answer: there's no benefit to be gained by adding an index on just that one boolean column.

That's because with only two values, if those values are distributed evenly, the index isn't going to improve performance. The index would only be useful if you are always looking for rows that have the value that occurs most infrequently, like less than 5% or 10% of the rows. In that special case, it might improve performance.

I strongly suspect that's not the only predicate in your query. Likely, there are other predicates, on other columns. And including that boolean column as an extra column in another index that will be used by your query, that might actually be of some benefit.

There's some overhead to creating and maintaining an index (it isn't free), so that has to be weighed against the benefit of having an index. Adding an extra column to an existing index would be lot less expensive.

The most appropriate indexes are going to depend on the tables you are querying, and the actual SQL statements you are executing.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • And on top of that TINYINT 1 isn't even boolean – Hanky Panky Nov 07 '14 at 00:12
  • @Hanky웃Panky: It's true that `TINYINT(1)` isn't really a boolean datatype. We can actually store 256 different values. (The length specifier doesn't restrict the range of values that can be stored, we'd need triggers to implement that kind of restriction.) But TINYINT(1) is a common target for ORM mapping of boolean. It's convenient because there aren't any problems in the client library with it. In our shiop, we use TINYINT(1) exclusively for boolean values. (NULL is NULL, 0 is FALSE, anything else is TRUE.) – spencer7593 Nov 07 '14 at 02:48
2

Without going into too much detail, the answer is that you should only consider indexing it if you are commonly searching for one of the boolean values AND that value is relatively rare.

That is: if very few of the total pages are "is_owned_by_admin=1" and you are doing searches that tend to want to find some significant number of those, then you might consider indexing. However even in that case, the overhead of the index might not be worth it.

Jas
  • 327
  • 2
  • 9