Tengo una tabla mysql con aproximadamente 10 millones de filas. Para cada fila, tengo una columna id y una columna date. La columna id no es única, y para una id hay varias filas con diferentes valores para date, generalmente de 3 a 6 fechas para cada id. Quiero seleccionar las filas con la última date para su id.

Mi consulta:

SELECT   id,
         date
FROM     tab a
WHERE    a.date = (SELECT MAX(date)
                   FROM   tab b
                   WHERE  a.id=b.id)

Es muy lento y tarda minutos en completarse. Se siente que esto podría hacerse mucho más rápido. ¿Cuáles son las mejores prácticas aquí?

3
cruvadom 9 may. 2019 a las 16:23

4 respuestas

La mejor respuesta

¿Por qué no solo?

SELECT   id,
         MAX(date) date
FROM     tab
GROUP BY id;
5
SASSY_ROG 9 may. 2019 a las 13:27

Asumiré que hay más columnas que desea obtener de la tabla (de lo contrario, la solución de DanB es la mejor manera de hacerlo).

Dividamos esto en dos pasos:

  1. Obtenga las fechas máximas para cada id
  2. Obtenga los datos requeridos

El primer paso es fácil:

SELECT id, max(date)
FROM tab
GROUP BY id

Importante: ambas columnas deben estar indexadas.

Ahora, el paso dos es la parte difícil. ¿Cómo obtener todos los datos que necesita?

Lo que haría es:

  • Cree una tabla temporal con el resultado de la consulta anterior,
  • Indexarlo y
  • Únete a la mesa con esta nueva mesa temporal.

Entonces intentemos:

CREATE TEMPORARY TABLE temp_dates
     SELECT id, max(date) as mdate
     FROM tab
     GROUP BY id;

ALTER TABLE temp_dates
     ADD UNIQUE INDEX u_id (id),
     ADD INDEX i_mdate (mdate);

SELECT a.id, a.date -- Add all the columns you need
FROM tab AS a
     INNER JOIN temp_dates AS b ON a.id=b.id and a.date=b.mdate;

Espero que esto ayude.

1
Barranka 9 may. 2019 a las 14:06

Ante todo: Los id deben ser únicos. Ese es su propósito.

Ahora mi recomendación:

select
  id,
  max(date) as latest_date
from a

join b
  on b.id = a.id

group by a.id

order by latest_date;
0
xxRMxx 9 may. 2019 a las 13:53

Su consulta podría ser la forma más rápida de abordar esto. Pero definitivamente necesita un índice en tab(id, date) para el rendimiento. Ambas columnas en el índice.

Si tiene otra tabla de identificadores, el siguiente es a menudo el enfoque más rápido:

select ids.id,
       (select max(t.date)
        from tab t
        where t.id = ids.id
       ) as max_date;

Esto requiere el mismo índice pero no requiere escanear la tabla completa (así como el índice).

0
Gordon Linoff 9 may. 2019 a las 14:10
56060431