Columnas combinadas

Esta sección trata de una confusión muy común que afecta a los índices concatenados.

El primer ejemplo es nuevamente acerca de los tipos de fechas y tiempo pero al revés. La siguiente sentencia MySQL combina un dato y una columna de tiempo para aplicar un rango filtrado sobre ambas columnas.

SELECT ...
  FROM ...
 WHERE ADDTIME(date_column, time_column)
     > DATE_ADD(now(), INTERVAL -1 DAY)

La sentencia selecciona todos los registros de las últimas 24 horas. Y no puede usar el índice concatenado (DATE_COLUMN, TIME_COLUMN) correcta­mente porque la búsqueda no está realizada sobre las columnas indexadas sino sobre datos derivados.

Es posible evitar este problema usando un tipo de datos, que contenga ambos: una fecha y un componente de tiempo (p.ej., MySQL DATETIME). Después se puede usar esta columna sin llamar una función:

SELECT ...
  FROM ...
 WHERE datetime_column
     > DATE_ADD(now(), INTERVAL -1 DAY)

Desafortunadamente, en general no es posible cambiar la tabla cuando nos enfrentamos a este problema.

La siguiente opción es un índice basado en una función si la base de datos lo soporta (aunque tienen muchas desventajas como vimos antes). Cuando se usa MySQL, de todos modos los índices basados en funciones no son una opción.

Es todavía posible escribir una sentencia con la que la base de datos pueda usar un índice concatenado sobre DATE_COLUMN, TIME_COLUMN con un acceso de predicado, por lo menos parcialmente. Para ello, se agrega una condición adicional sobre DATE_COLUMN.

 WHERE ADDTIME(date_column, time_column)
     > DATE_ADD(now(), INTERVAL -1 DAY)
   AND date_column
    >= DATE(DATE_ADD(now(), INTERVAL -1 DAY))

La nueva condición es absolutamente redundante pero es un filtro directo sobre DATE_COLUMN que puede usarse como acceso de predicado. Aunque esta técnica no es perfecta, es generalmente un buen método y suficiente.

Se puede también usar esa técnica cuando se almacenan datos y tiempo en columnas de texto, pero se tienen que emplear formatos de fecha y de tiempo que producen un orden cronológico cuando son ordenados de manera lexicográfica, por ejemplo, como sugiere el estándar ISO 8601 (YYYY-MM-DD HH:MM:SS). El siguiente ejemplo utiliza la función Oracle TO_CHAR para este propósito:

SELECT ...
  FROM ...
 WHERE date_string || time_string
     > TO_CHAR(sysdate - 1, 'YYYY-MM-DD HH24:MI:SS')
   AND date_string
    >= TO_CHAR(sysdate - 1, 'YYYY-MM-DD')

Vamos a enfrentarnos al problema aplicando una condición de rango sobre varias columnas nuevamente en la sección titulada “Paginar a través del resultado”. Se empleará también el mismo método de aproximación para atenuarlo.

A veces, tenemos el caso inverso y es posible que se desee usarlo para complicar intencionadamente una condición, pero no se podrá utilizar de ninguna manera como predicado de acceso. Ya se ha visto este problema al hablar de los efectos de las variables Bind sobre condiciones LIKE. Considera el siguiente ejemplo:

SELECT last_name, first_name, employee_id
  FROM employees
 WHERE subsidiary_id = ?
   AND last_name LIKE ?

Asumiendo que existe un índice sobre SUBSIDIARY_ID y uno más sobre LAST_NAME, ¿qué es lo más adecuado para esta sentencia?

Sin saber la posición del comodín en el criterio de la búsqueda, es imposible dar una respuesta cualificada. El optimizador no tiene otra elección que “suponer”. Si se sabe que siempre existe un comodín que encabeza la búsqueda, se puede complicar intencionadamente la condición LIKE para que el optimizador no pueda considerar el índice sobre LAST_NAME.

SELECT last_name, first_name, employee_id
  FROM employees
 WHERE subsidiary_id = ?
   AND last_name || '' LIKE ?

Basta con agregar una cadena de caracteres vacía sobre la columna LAST_NAME. Sin embargo, se trata de una opción de último recurso. Escógela solo si es absolutamente necesario.

Si te gusta mi manera de explicar, te encantará mi libro.