Is there any performance gain in indexing a boolean field?

mysql indexing innodb
Niet the Dark Absol picture Niet the Dark Absol · May 9, 2012 · Viewed 43.5k times · Source

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?

Answer

oucil picture oucil · Dec 10, 2013

Just to put a finer point on several other answers here, since in my experience, those looking at questions like this are in the same boat we were, we've all heard that indexing Boolean fields is pointless, and yet...

We have a table with about 4 million rows, only about 1000 or so at a time will have a Boolean switch flagged and that's what we search against. Adding an index on our Boolean field sped up queries by orders of magnitude, it went from about 9+ seconds to a fraction of a second.

Related questions

How to disable index in innodb

I'm trying to speed up bulk insert in an InnoDB table by temporary disabling its indexes: ALTER TABLE mytable DISABLE KEYS; But it gives a warning: +-------+------+-------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------+ | Note | 1031 | Table storage engine for …

Read More
MySQL Integer vs DateTime index

Let me start by saying I have looked at many similar questions asked, but all of them relate to Timestamp and DateTime field type without indexing. At least that is my understanding. As we all know, there are certain advantages …

Read More
How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More