Search code examples
mysqlindexinginnodb

Is there any performance gain in indexing a boolean field?

I'm just about to write a query that includes a WHERE isok=1. As the name implies, isok is a boolean field (actually a TINYINT(1) UNSIGNED that is set to 0 or 1 as needed).

Is there any performance gain in indexing this field? Would the engine (InnoDB in this case) perform better or worse looking up the index?

Solution

  • Not really. You should think about it like a book. If there were only 3 kinds of words in a book and you index all of them, you would have the same number of index pages as normal pages.

    There would be a performance gain if there are relatively few records of one value. For example, if you have 1000 records and 10 of them are TRUE, then it would be useful if you searching with isok = 1

    As Michael Durrant mentioned, it also makes writes slower.

    EDIT: Possible duplication: Indexing boolean fields

    Here it explains that even if you have an index, if you have too many records it doesn't use the index anyways. MySQL not using index when checking = 1 , but using it with = 0