Is there any performance gain in indexing a boolean field?

by

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.

More Related Contents:

  1. How to disable index in innodb
  2. MySQL Integer vs DateTime index
  3. MyISAM versus InnoDB [closed]
  4. With MySQL, how can I generate a column containing the record index in a table?
  5. How do I quickly rename a MySQL database (change schema name)?
  6. Is it possible to have function-based index in MySQL?
  7. MySQL AUTO_INCREMENT does not ROLLBACK
  8. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  9. How do MySQL indexes work?
  10. Best type of indexing when there is LIKE clause [duplicate]
  11. Does MySQL index foreign key columns automatically?
  12. Howto: Clean a mysql InnoDB storage engine?
  13. What does the KEY keyword mean?
  14. Restore table structure from frm and ibd files
  15. MySQL InnoDB not releasing disk space after deleting data rows from table
  16. Difference between Key, Primary Key, Unique Key and Index in MySQL
  17. What’s the difference between MyISAM and InnoDB? [duplicate]
  18. Why is MySQL InnoDB insert so slow?
  19. Prevent auto increment on MySQL duplicate insert
  20. What is cardinality in MySQL?
  21. What’s the difference between using INDEX vs KEY in MySQL?
  22. Bogus foreign key constraint fail
  23. How do I get MySQL to use an INDEX for view query?
  24. Mysql covering vs composite vs column index
  25. Higher cardinality column first in an index when involving a range?
  26. Database corruption with MariaDB : Table doesn’t exist in engine
  27. Defining Composite Key with Auto Increment in MySQL
  28. MySQL distinction between e and é (e acute) – UNIQUE index
  29. MySQL – why not index every field?
  30. Workaround in mysql for partial Index or filtered Index?

Leave a Comment