MySQL - Cuando utilizar tablas temporales o indices?

Estoy viendo que se pueden utilizar tablas temporales en MySQL, pero no entiendo muy bien cuando utilizarlas, ya que todos los ejemplos que he visto, es para reducir el número de registros que se utilizan al ejecutar una consulta... pero, si esa tabla inicial ya tienes sus indices, no es necesario utilizar una tabla temporal, no?

Es decir, no se en que momento hay que utilizar una tabla temporal... alguien me puede iluminar?

Hola joel:

Las tablas temporales usualmente se usan para dos cosas:

1. Hacer consultas sobre tablas donde no necesitas que la información "permanezca" en la Base de Datos. Un ejemplo podrías ser una tabla de Meses... podrías tener una tabla con los 12 meses del año para hacer un JOIN sobre alguna otra, pero no quieres que esa tabla de meses resida permanentemente en memoria, ya que no la vas a usar en un futuro.

2. Optimizar consultas cuando tus tablas "reales" sean MUY GRANDES. Por ejemplo si tienes una tabla histórica que guarda información de los últimos 5 años con 10 millones de registros, aun con el uso de índices podría resultar muy lento consultar la tabla, entonces podrías optar por extraer en una tabla temporal sólo la información del año que quieres consultar en una tabla temporal y sobre esa tabla temporal hacer las consultas.

Checa esta liga, creo que te puede ilustrar un poco más acerca del uso de este tipo de tablas.

http://edgartec.com/mysql-optimizar-consultas-usando-tablas-temporales-en-memoria/

Además, San Google tiene muchos otros ejemplos de cómo puedes usar este tipo de tablas.

Saludos
Leo.

Hola Leonardo, es un muy buen ejemplo el que has puesto en el primer punto, estoy completamente de acuerdo contigo en ese punto, pero no en el segundo punto.

A lo mejor estoy equivocado, pero en el segundo punto, para extraer los datos a la tabla temporal tardara lo mismo que si hiciera la consulta directamente a esa tabla sin pasar por la tabla temporal, no?
Es decir, lo que tarda en generar la tabla temporal, seria el mismo tiempo que utilizaría mi consulta utilizando directamente la tabla con los 10 millones de registros, no?

Lo veo como hacer un paso mas para llegar al mismo final... seguramente estoy equivocado, pero no se ver la ventaja en ese segundo punto.

Creo que LEONARDO_JOSUE se refiere a que, tras la primera consulta que recoge los registros del año a seleccionar, las siguientes consultas a la tabla temporal deberían ser mucho más rápidas al leer únicamente los registros ya filtrados.

Sobre este tema ¿cómo puedo acceder a la estructura de esa tabla temporal?

Lo digo porque tengo un asistente que lee la información de las tablas de la base de datos consultando el esquema INFORMATION_SCHEMA de MySQL, consultando varias tablas como TABLES, COLUMNS y otras.
Me es muy cómo programar accediendo a los nombres de campos, estructuras de índices, etc. y con las tablas temporales, obviamente no he encontrado esa información.

Un ejemplo de este asistente:

Hola elhabraque:

No es correcto utilizar la pregunta de otro usuario para hacer una consulta propia, ya que si bien estamos hablando de tablas temporales en ambos casos, en realidad son dos cosas COMPLETAMENTE DISTINTAS. Te sugiero que abras una nueva entrada con tu duda y con gusto tratamos de ayudarte. No mezclar temas por favor.

Saludos
Leo.

Hola de nuevo Joel:


Tal como lo planteas, es correcto, pero creo que estás cerrando tu mente a sólo un ejemplo poco práctico donde hacer UNA consulta es lo mismo si usas la tabla temporal que la tabla original, pero te voy a plantear un ejemplo para ver si se entiende en dónde está la utilizad de las tablas temporales.

Imagina que tienes una tabla donde guardas la información de los censos de población de un pais... (no nos dices donde vives, pero por lo general, todos los paises hacen un censo de población cada 10 años)

entonces, en una tabla deberías de tener la información DE CADA PERSONA QUE VIVE EN UN PAIS. entonces en tu tabla tienes el siguiente resumen:


Imagina que ahora necesitas ejecutar 100 consultas para obtener la información referente al más reciente censo de población.

Podrías entonces hacer una consulta así:

etc...

entonces CADA CONSULTA QUE HAGAS LO HARÍAS CONTRA UNA TABLA DE 150 MILLONES DE REGISTROS, correcto???

Pero ¿qué pasa entonces si haces una tabla TEMPORAL?:


entonces tus consultas podrías hacerlas así:


en este caso la tabla ORIGINAL SÓLO SE CONSULTA UNA VEZ con sus 150 millones de registros... El resto de las consultas se hacen sobre una tabla que tiene sólo los 45 millones de registros que realmente te interesan.

¿Se entiende?

Insisto, si piensas sólo en ejecutar UNA CONSULTA DA LO MISMO, pero te recuerdo que en este caso, las tablas temporales PERMANCEN EN MEMORIA hasta que se cierra la sesión, por lo que puedes seguir utilizándolas la cantidad de veces que quieras.

Finalmente, la mejor forma de verificar si esto es como te digo es haciendo una práctica. si no tienes tablas con millones de registros, puedes crearlas con datos DUMMY y hacer tus propias pruebas y sacar tus propias conclusiones. Y si lo haces, entonces nos muestras los resultados y nos dices quién tenía razón.

Saludos
Leo.

Hola Leonardo, muchas gracias por tus comentarios, entiendo perfectamente lo que me dices, si voy a hacer muchas consultas a esa tabla, mejor utilizar una temporal... lo entiendo.

Pero te hago una pregunta...
Cuando yo ejecuto esta consulta:
Si tengo la tabla indexada por el año (anio), me indica que únicamente esta moviendo los 45 millones de registros, no los 150 millones.

Es ahí donde esta mi duda... al utilizar un índice, MySQL solo mueve los registros necesarios... por eso no entiendo muy bien la finalidad de la tabla temporal.


Agradezco mucho la información, de verdad.