Por José Luis Rodríguez Alonso
Todos sabemos que para agilizar una consulta SQL podemos usar índices. Un índice ayuda a la base de datos a buscar lo que queremos sin tener que analizar una a una las filas de una tabla. Pero, ¿sabías que hay algunas situaciones en las que el índice se anula sin avisar? Es lo que se conoce como “index obfuscation”, aquí te describo algunos de los casos más comunes:
Caso 1: Filtrar usando funciones
El índice no se tiene en cuenta cuando filtras utilizando una función sobre una columna con índice. Por ejemplo, si tenemos un índice en la columna users.email
y queremos hacer una consulta para filtrar por la palabra test
, sin tener en cuenta las posibles mayúsculas, podríamos hacer algo como:
|
|
El problema es que al hacer un LOWER(email)
, la base de datos ya no puede hacer uso del índice de users.email
y tiene que hacer la búsqueda en toda la tabla.
Hay varias soluciones a esto, para este caso en concreto, podríamos utilizar un collation
o tipo de dato en la columna afectada que sea case-insensitive
. Otra posible solución es crear un índice para nuestro caso particular, un “functional index”:
|
|
Utilizando el EXPLAIN
o EXPLAIN ANALYSE
en Postgres se ve una mejora considerable:
LOWER(email)
: Execution Time: 28.957 ms
Execution Time: 0.088 ms
Caso 2: Filtrar usando operaciones
Otro caso típico es el de tener un índice en una columna y hacer operaciones con esa columna a la hora de filtrar, por ejemplo:
|
|
La base de datos tiene un índice en la columna price
pero no en price / 100
, por lo que el índice se anula y la base de datos tiene que hacer esa operación con todas las filas. En este caso la solución no sería crear un nuevo índice, sino mover la operación a la derecha:
|
|
Caso 3: Filtrar usando diferentes tipos
Dependiendo de la base de datos y el lenguaje de programación que utilices, es posible que estés ignorando los índices sin saberlo si utilizas los tipos incorrectos.
Por ejemplo, imagina una tabla de products
con un código numérico pero que, por cualquier motivo, se ha decidido definir como VARCHAR
en la base de datos. Si en el código de tu aplicación o en la consulta SQL pasas el tipo incorrecto, la base de datos tendrá que hacer una conversión y también ignorará el índice.
|
|
Todos estos casos son fáciles de pasar por alto durante el desarrollo, sobre todo si trabajamos con bases de datos de prueba con pocos datos.
Siempre que hagas una consulta SQL es recomendable echarle un ojo al “explain plan”, porque quizás la base de datos esté trabajando más de lo necesario.
También es imprescindible contar con una buena capa de observabilidad para detectar estos problemas lo antes posible.
¿Conoces otros casos que puedan afectar a nuestros índices?
¿Quieres más? te invitamos a suscribirte a nuestro boletín para avisarte cada vez que recopilemos contenido de calidad que compartir.
Si disfrutas leyendo nuestro blog, ¿imaginas lo divertido que sería trabajar con nosotros? ¿te gustaría?
Pero espera 🖐 que tenemos un conflicto interno. A nosotros las newsletter nos parecen 💩👎👹 Por eso hemos creado la LEAN LISTA, la primera lista zen, disfrutona y que suena a rock y reggaeton del sector de la programación. Todos hemos recibido newsletters por encima de nuestras posibilidades 😅 por eso este es el compromiso de la Lean Lista