Indices en tablas derivadas.

En el explain te recomienda cuales son los posibles ínices que puedes generar para mejorar los cruces entre tablas.

Siempre crea un índice de llave primaria para cada tabla (si cada registro de esta es único) Considera revisar cuales son los campos por los cuales puedes usar en consultas de relación y hazlas índices y por otro lado verifica que campos se usarán para búsquedas y también trata de indexarlas.

Te recuerdo que esto es un trabajo de análisis por lo que no debes indexar todos los campos de tu tabla ya que provocaría una gran cantidad de peso en el motor de la base de datos.

Indexa solo en campos de lentitud de generación de data y en tablas de búsqueda recurrente.

Me estás diciendo obviedades, pero no me contestar lo que digo ... ¿cuando utilizas tablas derivadas cómo tratarlas?, esas tablas no existen ...cómo hacer un índice o algo similar en ellas ...

Es que esas obviedades son la base de lo que necesitas crear, no hay mas ciencia en hacer un análisis en cada consulta que generas y observar los resultados de los explain adicionando lógica necesaria para establecer relaciones acordes a lo que necesitas potenciar.

Son obviedades porque estoy en mi escritorio y no tengo idea de cual es el contexto de tu proyecto y para adivino no he estudiado tantos años, por lo que tengo que hacerte comentarios con responsabilidad para que no arruines lo que has construido armando hipótesis sin fundamento que te pueden llevar a hacer acciones destructivas.

No olvides que las tablas derivadas se generan en función de sentencias simples de sql anidadas en otras consultas a un "nivel superior" cuando haces esto por ejemplo

SELECT
 *
FROM
(SELECT
T1. Columna1, T2.Columna2 FROM MiTabla1 T1 JOIN MiTabla2 T2 ON 
T1.Columna1 = T2.Columna1 WHERE MiCondición) AS MiTablaDerivada

Tienes que considerar T1.Columna1 = T2. Columna1 que son las llaves que realizan el join si una de las dos tablas contiene millones de registros y no está indexada obviamente su performance será deficiente. Por otro lado sería conveniente validar si los campos que están en "MiCondicion" son necesarios de revisar en función de su velocidad de respuesta en la búsqueda.

Por ultima opción, hay que validar cuando se usa la consulta para ver si es conveniente indexar una consulta que se usará una vez al mes en la noche o a diario por cada usuario que revisa la web y como se comportará cuando tenga el doble o 10 veces más registros.

A mayor cantidad de campos de union, mayor es la cantidad de posibles llaves a indexar.

Esta decisión la asume un dba estudioso del motor de base de datos que usas, que ha estudiado posteriormente la estructura de tu modelo de base de datos y sus estructuras para decidir en función de estas obviedades cuando es necesario indexar o no estos campos.

Lo que me pides es un tema delicado porque es parte de la espina dorsal de tu aplicación, si quieres soluciones mágicas con artificio, por mi parte sostengo que no existen. Por lo mismo un dba estudia años sobre teoría de conjuntos para analizar recién las relaciones, sin considerar el tiempo para entender el software del motor de base de datos.

No pido magia, sino opinión ... y el ejemplo que me pones no es el caso, aprovecharé tu ejemplo para añadir lo que le falta:

SELECT
*
FROM
(SELECT
T1.Columna1, T2.Columna2 FROM MiTabla1 T1 JOIN MiTabla2 T2 ON
T1.Columna1 = T2.Columna1 WHERE MiCondición) AS MiTablaDerivada inner join MiTablaFisica on MiTablaDerivada.Columna1=MiTablaFisica.Columna0

MiTabla1 y MiTabla2, evidentemente han de estar indexadas, pero que lo estén no quiere decir que MiTablaDerivada lo esté, por lo que el inner join exterior sirve para enlazar, para decir que tal campo de la derivada debe corresponderse con tal campo de la tabla física pero nada mas ...

MiTablaFisica, si que está indexada, al igual que MiTabla1 y MiTabla2, por lo que la join entre estas dos será óptima pero no entre su resultado (MiTablaDerivada) y MiTablaFisica, la cuestión es cómo hacer eso.

Es que no es posible indexar algo que está en un buffer de memoria. Los resultados de una consulta no pueden manipularse en ese estado.

Para eso existen vistas o hacer tablas de paso, las cuales puedes indexar y alimentar por medio de procedimientos almacenados.

Creas una tabla y con el procedimiento almacenado elimina la data antigua (si es necesario) y agregas la data nueva con el resultado de la consulta que ya manejas, de esta forma puedes indexar esta tabla, al ser una tabla real y concreta.

Las vistas no las puedes indexar (por lo que se), pero trabajan de manera más eficiente que una consulta tal cual estas trabajando actualmente.

Tal cual te he comentado anteriormente, la optimización no siempre está en la consulta misma que estas realizando, si no en todas las estructuras que interactúan con esa query, revisa el espectro con el cual trabajas, recopila posibles soluciones y amolda lo que necesitas para tu mejor escenario.

Pues ahí está el tema ... además tras las pruebas que he realizado mientras que esto si es posible: "create table resultados select * from alumnos where nombre like 'a%'" una tabla temporal no se puede crear de esa manera ...

El tema de las vistas en principio, ralentizan todo el sistema, ya que se deben actualizar a cada inserción, eliminación, de registros, y las tablas temporales no creo que me funcionasen para mucho, ya que la sql es de una sola ejecución, luego ya se trabaja sobre esos resultados ... y evidentemente no puedo dejar la sql en 11 segundos ...

¿Has hecho testeos reales con vistas y tablas temporales?

Quizá en tu caso habría que evaluar las alternativas que te comento y ver sobre cual se trabaja con mejor rendimiento y optimizar desde ahí.

Las tablas temporales que usas están a merced de las tablas "reales" sobre las cuales trabajas, como te he comentado tendrás que optimizar esas tablas ya que no puedes mejorar el rendimiento de algo que está en memoria.

No, no he realizado pruebas reales con vistas. He indexado las tablas que intervienen en la consulta grande, por los campos que se utilizan para las join luego he restringido alguna consulta más, y de 230 segundos, lo pasé a 100 y de ahí a 1.5 segundos ... Lo que me preocupa es que tengo pocos registros y tarda eso, cuando tenga una cantidad considerable no sé qué pasará ... También diré, que al poner los indices, he tenido que eliminar registros que no tenían correspondencia en las tablas "maestras".

Lo que me sorprende de éstas cosas, es que la diferencia entre poner índices o no ponerlos es poca, al menos siempre que lo he hecho ... No sé que opinión te merece esto último.

Genera diferencia, aunque dependerá del modelo de base de datos y su relación.

Como te comentaba anteriormente, si realizas un explain te puede decir que tipo de "recorrido" está elaborando para armar las tuplas. Si tu consulta está realizando un "full scan" sobre una tabla muy poblada es probable que no exista forma de optimizarla a no ser de intervenir la tabla con indices o mejorar la indexación desde la consulta aplicando filtros más específicos y de corto recorrido.

No discrimines opciones como las vistas sin antes testear su comportamiento (puede ser que te lleves una sorpresa).

Revisa con el explain cuales son las partes donde está demorando o trabajando en mayor cantidad tu consulta y ve testeando.

Esto de la optimización exhaustiva que elaboras requiere demasiado trabajo.

Bueno lo del explain, ..., es la primera vez que lo utilizo y no me parece gran cosa, me refiero a que no te marca tiempos, te dice eso si, si utiliza el index cual o si utiliza el where, el número de registros que trabaja esa select, te dice las "possible keys", que podrías utilizar y/o definir ...

No descarto el uso de vistas, pero lo que pasa es que entonces no podría trabajar como hasta ahora, p.ej, las sentencias "select" interiores tienen condicionantes que se le pasan desde el programa, si tuviera que pasárselas a una vista para que las "selects" exteriores tuveran la información actualizada, en vez de utilizar un "command" desde el programa, tendría que genera un "store procedure" para que todo fuera "acompasado" ... y probablemente, quizá tuviera que utilizar cursores ... ... no sé si me explico.

No entiendo tu punto de "vista" con respecto a las vistas.

Siendo una vista una tabla generada por una consulta, a lo que retorna una estructura de columnas y registros, sería posible hacer un select sobre esa consulta generando instancias de filtro como where.

http://www.mysqltutorial.org/create-sql-views-mysql.aspx

Otra posibilidad mas compleja pero puede ser más liviana es armar la lógica en capa de "modelo" o sea, tomar las tablas en bruto y depositarlas en arrays de php y desde ahí generar la lógica para hacer las tuplas.

Es algo complejo pero en función del algoritmo y de la cantidad de data rescatada en las consultas puede en escenarios muy especiales llegar a ser más liviano que mysql.

Que sí, que entiendo lo de las vistas, que las he utilizado ... pero tampoco las puedes indexar, que si, que la vista ya está generada ...

Lo que si he probado es el tema de "forzar" utilizar un índice, el tema de p.ej:

select * from alumnos use index(FK_idClase) ...

Lo de la posibilidad más compleja que me comentas, es poner toda la información en memoria y trabajar ahí ... pues si ... pero si el usuario estuviera haciendo otras cosas, eso le ralentizaría el equipo y todo lo demás que estuviera haciendo ...

Ya te digo de momento lo he reducido a 1.5 segundos, lo que antes eran casi 4 minutos ...

De todas maneras, el tema de generar sql's es algo que me divierte, y trataré de optimizarla más, con todo lo que me has dicho, lo que se me ocurra, y lo que pueda captar por otros lados ...