soporta - optimizar consultas lentas mysql

Cómo recuperar de manera eficiente los datos en una a muchas relaciones

Question

Me encuentro con un problema donde tengo la necesidad de ejecutar una consulta que debe obtener algunas filas de una tabla principal, y tengo un indicador si la clave de la tabla principal existe en una subtabla (relación uno a muchos).

La consulta podría ser algo como esto:

select a.index, (select count(1) from second_table b where a.index = b.index)
from first_table a;

De esta forma obtendría el resultado que quiero (0 = no depende de los registros en la segunda tabla, de lo contrario hay), pero estoy ejecutando una subconsulta para cada registro que obtengo de la base de datos. Necesito obtener ese indicador para al menos tres tablas similares, y la consulta principal ya es una combinación interna entre al menos dos tablas ...

Mi pregunta es si hay alguna manera realmente eficiente de manejar esto. He pensado en mantener el registro en una nueva columna como "first_table", pero el dbadmin no permite los desencadenantes y el seguimiento del mismo por código es demasiado arriesgado.

¿Cuál sería un buen enfoque para resolver esto?

La aplicación de esta consulta será por dos cosas:

1. Indique que existe al menos una fila en second_table para una fila dada en first_table. Es para indicarlo en una lista. Si no existe una fila en la segunda tabla, no encenderé este indicador.
2. Para buscar todas las filas en first_table que tienen al menos una fila en second_table, o que no tienen filas en la segunda tabla.

Otra opción que acabo de encontrar:

select a.index, b.index
from first_table a
left join (select distinct(index) as index from second_table) b on a.index = b.index

De esta forma obtendré nulo para b.index si no existe (la pantalla finalmente puede ser adaptada, me preocupa el rendimiento de la consulta aquí).

El objetivo final de esta pregunta es encontrar un enfoque de diseño adecuado para este tipo de casos. Sucede a menudo, una aplicación real podría ser un sistema POS que muestre a todos los clientes y tenga un ícono en la lista como indicador de que el cliente tiene órdenes abiertas.

Answer 1

Supongo que no puede cambiar las definiciones de la tabla, por ejemplo, particionar las columnas.

Ahora, para obtener un buen rendimiento, debe tener en cuenta otras tablas que se están uniendo a su tabla principal.

Todo depende de los datos demográficos.

1. Si las otras combinaciones colapsarán las filas por factor alto, debería considerar hacer una unión entre su primera mesa y la segunda. Esto permitirá que el optimizador elija el mejor orden de unión, es decir, que primero se una a otras tablas, luego las filas resultantes se unirán a su segunda tabla para obtener el rendimiento.

2. De lo contrario, puede tomar un enfoque de subconsulta (sugiero que usar existe, puede ser la solución de Mikhail).

3. Además, puede considerar crear una tabla temporal, si necesita más de una vez en la misma sesión.

Answer 2

Trate de usar EXISTS, supongo, para tal caso podría ser mejor que unir mesas. En mi oráculo DB, el tiempo de ejecución es ligeramente mejor que la consulta de muestra, pero puede ser específico de db.

SELECT first_table.ID, CASE WHEN EXISTS (SELECT * FROM second_table WHERE first_table.ID = second_table.ID) THEN 1 ELSE 0 END FROM first_table

Answer 3

por qué no probar este

select a.index,count(b.[table id])
from first_table a
left join second_table b
on a.index = b.index
group by a.index

Answer 4

La aplicación de esta consulta será por dos cosas:

1. Indique que existe al menos una fila en second_table para una fila dada en first_table. Es para indicarlo en una lista.
2. Para buscar todas las filas en first_table que tienen al menos una fila en second_table.

Aqui tienes:

SELECT a.index, 1 as c_check -- 1: at least one row in second_table exists for a given row in first_table
FROM first_table a
WHERE EXISTS
(
SELECT 1
FROM second_table b
WHERE a.index = b.index
);

Answer 5

Dos ideas: una que no implique cambiar sus tablas y otra que sí lo haga. Primero, el que usa sus tablas existentes:

SELECT
a.index,
b.index IS NOT NULL,
c.index IS NOT NULL
FROM
a_table a
LEFT JOIN
b_table b ON b.index = a.index
LEFT JOIN
c_table c ON c.index = a.index
GROUP BY
a.index, b.index, c.index

Vale la pena señalar que esta consulta (y probablemente cualquiera que se le parezca) será de gran ayuda si b_table.index y c_table.index son claves principales o indexadas de otra forma.

Ahora la otra idea. Si puede, en lugar de insertar una fila en b_table o c_table para indicar algo acerca de la fila correspondiente en a_table , indíquelo directamente en la fila a_table . Agregue las columnas exists_in_b_table e exists_in_c_table a a_table . Siempre que inserte una fila en b_table , establezca a_table.exists_in_b_table = true para la fila correspondiente en a_table . Las eliminaciones son más a_table ya que para actualizar la fila a_table debe verificar si hay filas en b_table distintas a la que acaba de eliminar con el mismo índice. Sin embargo, si las eliminaciones son poco frecuentes, esto podría ser aceptable.

Answer 6

O puede evitar unirse por completo.

WITH comb AS (
SELECT index
, ''N'' as exist_ind
FROM first_table
UNION ALL
SELECT DISTINCT
index
, ''Y'' as exist_ind
FROM second_table
)
SELECT index
, MAX(exist_ind) exist_ind
FROM comb
GROUP BY index

Answer 7

No soy experto en el uso de mayúsculas y minúsculas, pero recomendaré la unión ...

eso funciona incluso si usa tres tablas o más ...

SELECT t1.ID,t2.name, t3.date
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 ON t1.ID = t2.ID
LEFT OUTER JOIN Table3 t3 ON t2.ID = t3.ID
--WHERE t1.ID = @ProductID -- this is optional condition, if want specific ID details..

esto te ayudará a buscar los datos de las tablas normalizadas (BCNF), ya que siempre clasifican los datos con el tipo de naturaleza en tablas separadas.

Espero que esto haga ...