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?
See Question&Answers more detail:os
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.
