This will give you a covering index for the query (the EXPLAIN will show "Using index") which means the query can be satisfied from the index. Replace this predicate: TIMESTAMPDIFF(MINUTE, lastPositionTime,now()) = NOW() + INTERVAL -5 MINUTE ON (deviceId, lastPositionTime, speed)Īnd change the query. If you can give me any help i would be very happy :).Īdd an index. The idea of this it's the following: If the speed goes bigger than 60kmh for 5 times in a 5 minutes lapse, i need to know the device's id, max speed and the number of times that exceeded the speed limit. The purpose of the query it's to get the device's id and the number of times than the speed it's bigger than 10 (that's just an example, it could be more) and get the max speed recorded by the database. ) ENGINE=MyISAM AUTO_INCREMENT=6562682 DEFAULT CHARSET=latin1 `speed` int(11) NOT NULL COMMENT 'Velocidad del vehículo. `gradeLat` varchar(100) DEFAULT NULL COMMENT 'Latitud transformada a grados (en decimal).', `gradeLon` varchar(100) DEFAULT NULL COMMENT 'Longitud transformada a grados (en decimal).', `direction` int(11) DEFAULT NULL COMMENT 'Dirección en que apunta el dispositivo.', `latitude` int(11) NOT NULL COMMENT 'Latitud.', `divisionns` varchar(2) DEFAULT NULL COMMENT 'Orientación Norte o Sur.', `longitude` int(11) NOT NULL COMMENT 'longitud.', `divisionew` varchar(2) DEFAULT NULL COMMENT 'Orientación Este u Oeste.', `lastPositionTime` datetime NOT NULL COMMENT 'Fecha/hora en que se registra la marca de posición (realizada por el dvr).', Identificador único para cada vehículo.', `deviceId` int(5) NOT NULL COMMENT 'Id numérico del equipo. `idPosition` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id autoincremental.', This is the create table query: CREATE TABLE `regPosition` ( The table's type is Myisam and it has about 2M registry and has idPosition as a index. I'm using this query: select viceId, count(regPosition.speed), max(regPosition.speed) from regPosition where (TIMESTAMPDIFF(MINUTE, lastPositionTime,now()) = '10' group by viceId I add some atributes then i had found in some post but i think i just made it worse. I have made some search in the web about it so i'm posting the mysql's config file. I'm running the mysql server in ubuntu by a java service but even if i execute the query via any mysql ide the results are the same. I'm having some truobles in with this query, everytime when i use it the cpu usage goes from a 5% to 67%-100%.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |