Query demora demasiado

[E_Blue]

Hola, tengo la siguiente query que funciona ok.

Código MySQL:

SELECT deviceID,timestamp
 FROM EventData WHERE accountID='118901F' AND deviceID IN('CPP913','JJT385') AND timestamp>(unix_timestamp(now()-interval 10 minute));
 

accountID,deviceID, timestamp son primary key.

Sin embargo cuando cambio el filtro de timestamp a creationTime demora una eternidad.

Código MySQL:

SELECT deviceID,timestamp
 FROM EventData WHERE accountID='118901F' AND deviceID IN('CPP913','JJT385') AND creationTime>(unix_timestamp(now()-interval 10 minute));
 

Actualmente hay mas de 26 millones de registros en esa tabla pero puede llegar a mas de 100 millones.

¿Existe alguna forma de optimizar esta query?
¿Debería hacerla de otra manera?

[ComprasDesdeCasa]

Hola,

tienes indexados esos campos?

Saludos.

[E_Blue]

Si con indexar te refieres a primary key, están indexados todos los campos que uso en el WHERE excepto creationTime, ahí es cuando el motor MySQL se atora.

Básicamente no procesa otros SELECT hasta que termina con las querys que postee.

¿Será que habrá alguna forma de que no se detengan el resto de las querys?

Otro dato, esa tabla es muy activa, ademas de muy grande, y cambia constantemente varias veces por segundo.

[angelsds]

utiliza inner join es mas facil de procesar que plano carteciano

[jurena]

E_Blue,
no sé por qué restas ese dato así, ni siquiera en la primera consulta que dices que te funciona bien.
A un UNIX_TIMESTAMP de fechahora actual le restas 10 minutos. El resultado son segundos desde '1970-01-01 00:00:00' hasta ese momento, y el filtro trabaja sobre un timestamp en tu primera consulta, un timestamp. Dinos la razón de las dos consultas tal cual. Qué es lo que quieres buscar por lo que a la fecha hora se refiere... Así podremos ayudarte mejor.

[E_Blue]

angelsds, gracias por responder, nunca use inner joins, voy a leer un poco a ver como es el tema.

jurena, gracias por responder, en esa tabla tengo eventos que se van guardando de alarmas de clientes que tienen un dispositivo que se reporta a el servidor de la empresa para la que trabajo; en timestamp se guarda la fecha (UNIX) en la que se creo el reporte y en creationTime la fecha(UNIX) en la que el reporte llego al server.

El problema es que necesito "ver" los reportes de los últimos 10 minutos, por orden de llegada a la base(creationTime), no por fecha de creación(timestamp) pues suele pasar que hay reportes que llegan tarde porque no pudieron ser enviados en el momento en el que se generaron en el dispositivo remoto, quedan en memoria y son enviados al server mas tarde, es decir, llegan desordenados(timestamp).

Por lo tanto si ordeno por timestamp muchos eventos pueden perderse, por eso necesito ordenarlos por creationTime.

[jurena]

Si guardas en los dos campos UNIX de un timestamp, guardas un dato numérico. ¿Es así? ¿No puedes crear un índice para ese campo creationTime, digo un índice de tipo normal?

[E_Blue]

Los únicos indices que conozco son los primary key, ahora mismo estoy leyendo sobre los indices que me comentas, no sabia de su existencia.

[E_Blue]

Bueno, quise crear el indice en base a creationTime y a los 5 minutos me da error, me dice...


ERROR 2013: Lost connection to MySQL server during query
SQL Statement:
ALTER TABLE `base1`.`EventData`
ADD INDEX `creationTimeIndex` USING BTREE (`creationTime` DESC)
ERROR: Error when running failback script. Details follow.
ERROR 2006: MySQL server has gone away

Lo intente 2 veces, con el mismo resultado.

[jurena]

¿Qué motor de tablas usas, MyIsam o InnoDB? ¿Ese campo creationTime es Foreign Key en alguna otra tabla?
Por otra parte, un proceso de indexado de 26 millones de registros no es algo rápido, imagino, y probablemente el servidor tiene un límite para un operación como esta en vivo. ¿Desde dónde llevas a cabo la acción, desde consola, desde PHPMyAdmin, con un script PHP...?

[E_Blue]

La tabla es MyISAM.
El indice lo creo desde MySQL Workbench.

La base no la cree yo, pero creo que creationTime no es Foreing key

[jurena]

E_Blue,
El problema es complejo. Lee aquí y verás las respuestas.
http://stackoverflow.com/questions/3...-18-hours-or-f

[E_Blue]

Gracias por la info, ya estoy leyéndola, y por lo poco que leí hasta ahora puede ser, en parte, problema de configuración de la base y/o el motor MySQL.