¿Hay alguna ganancia de rendimiento en la indexación de un campo booleano?

Solo para poner un punto más fino en varias otras respuestas aquí, ya que en mi experiencia, aquellos que miran preguntas como esta están en el mismo barco que nosotros, todos hemos escuchado que indexar campos booleanos no tiene sentido, y sin embargo...

Tenemos una tabla con alrededor de 4 millones de filas, solo alrededor de 1000 aproximadamente a la vez tendrán un interruptor booleano marcado y eso es lo que buscamos. Agregar un índice en nuestro campo booleano aceleró las consultas por órdenes de magnitud, pasó de aproximadamente 9 + segundos a un fracción de segundo.

Depende de las consultas reales y de la selectividad de la combinación índice/consulta.

Caso A : condición WHERE isok = 1 y nada más allí:

SELECT *
FROM tableX
WHERE isok = 1

• Si el índice es lo suficientemente selectivo (digamos que tiene 1M filas y solo 1k tiene isok = 1), entonces el motor SQL probablemente utilizará el índice y será más rápido que sin él.

• Si el índice no es lo suficientemente selectivo (digamos que tiene 1M filas y más de 100k tienen isok = 1), entonces el SQL engine probablemente no utilizará el índice y hará un análisis de tabla.

Caso B: condición WHERE isok = 1 y más cosas:

SELECT *
FROM tableX
WHERE isok = 1
  AND another_column = 17

Entonces, depende de qué otros índices tenga. Un índice en another_column probablemente sería más selectivo que el índice en isok que tiene solo dos valores posibles. Un índice en (another_column, isok) o (isok, another_column) sería aún mejor.

No, normalmente no.

Normalmente indexas los campos para buscar cuando tienen alta selectividad/cardinalidad. La cardinalidad de un campo booleano es muy baja en la mayoría de las tablas. También haría que tus escrituras fueran fraccionalmente más lentas.

Sí un índice mejorará el rendimiento, verifique la salida de EXPLAIN con y sin el índice.

De los documentos:

Los índices se utilizan para encontrar filas con valores de columna específicos rápidamente. Sin un índice, MySQL debe comenzar con la primera fila y luego leer toda la tabla para encontrar las filas relevantes. Cuanto más grande sea la mesa, más cuesta esto. Si la tabla tiene un índice para las columnas en cuestión, MySQL puede determinar rápidamente la posición a buscar en el medio del archivo de datos sin tener que mirar todos los datos.

Creo que también es seguro decir que un índice no DISMINUIRÁ el rendimiento en este caso, por lo que solo tiene que beneficiarse de él.

Depende de la distribución de los datos.

Imagine que tenía un libro con 1000 páginas cuidadosamente mecanografiadas, y las únicas palabras en mi libro eran " sí " y " no " repetidas una y otra vez y distribuidas al azar. Si se me pidiera que rodeara todas las instancias de 'sí', ¿ayudaría un índice en la parte posterior del libro? Depende.

Si hubiera una distribución mitad y mitad aleatoria de sí y no, entonces buscar en el índice no ayudaría. El índice haría el libro mucho más grande, y de todos modos sería más rápido solo para comenzar desde el frente y trabajar mi camino a través de cada página en busca de todas las instancias de 'sí' y rodearlos, en lugar de buscar cada elemento en el índice y luego tomar la referencia de la entrada del índice a la página a la que se refiere.

Pero si hubiera, digamos, solo diez casos de ' sí 'en mi libro de mil páginas y todo lo demás fuera solo millones de no, entonces un índice me ahorraría mucho tiempo en encontrar esos diez casos de' sí ' y rodeándolos.

Es lo mismo en las bases de datos. Si se trata de una distribución 50:50, entonces un índice no va a ayudar - el motor de la base de datos es mejor simplemente arar a través de los datos de principio a fin (análisis de tabla completa), y el índice solo haría la base de datos más grande, y más lento para escribir y actualizar. Pero si es algo así como una distribución 4000:1 (según oucil en este hilo), entonces una búsqueda de índice puede acelerarlo enormemente, si es el 1 en 4000 elementos que está buscando para.

En realidad esto depende de las consultas que ejecute. Pero, generalmente sí, así como indexar un campo de cualquier otro tipo.