Slow queries with large table
Hi everybody, I have a problem querying large tables with MySQL. I have a main table called "Historico", that have a lot of rows (17 millions). This table is used to store historical data, so I have only inserts operations (no update or delete).
Here is my table:
CREATE TABLE `historico` (
`id` int(10) NOT NULL auto_increment,
`id_GPS` int(10) default NULL,
`fh_rec` datetime default NULL,
`fh_prod` datetime default NULL,
`id_key` smallint(5) default NULL,
`lat` double default NULL,
`long` double default NULL,
`n_s` varchar(1) default NULL,
`e_o` varchar(1) default NULL,
`cant_sat` smallint(5) default NULL,
`km` double default NULL,
`velocidad` smallint(5) default NULL,
`estados` int(10) default NULL,
PRIMARY KEY (`id`),
KEY `id_GPS` (`id_GPS`),
KEY `id_key` (`id_key`),
KEY `fh_prod` (`fh_prod`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I have 2 secondary tables thats store other information, related to the problem:
CREATE TABLE `vehiculos` (
`id_gps` int(10) NOT NULL,
`nombre` varchar(30) default NULL,
`matricula` varchar(20) default NULL,
`descripcion` varchar(35) default NULL,
`id_empresa` int(10) NOT NULL,
`tipoVehiculo` int(10) default 0,
PRIMARY KEY (`id_gps`,`id_empresa`),
KEY `id_empresa` (`id_empresa`),
KEY `id_gps` (`id_gps`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `usuariosweb` (
`idUsuario` varchar(50) NOT NULL,
`pwdUsuario` varchar(30) NOT NULL,
`idEmpresa` int(10) default NULL,
`nombre` varchar(50) default ' ',
`apellido` varchar(50) default ' ',
PRIMARY KEY (`idUsuario`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
When I execute this query, it takes more than 5 minutes!
SELECT DISTINCT h.id_GPS, h.lat, h.long, h.fh_prod, h.velocidad, h.km, h.estados, v.tipoVehiculo
FROM Historico h, Vehiculos v, UsuariosWeb uw
WHERE (h.id_GPS = v.id_gps) AND
(h.id_GPS in (64)) AND
(v.id_empresa = 0) AND
(uw.idUsuario = 'julio') AND
(uw.idEmpresa = v.id_empresa) AND
(h.fh_prod BETWEEN '2013-11-30' AND '2013-11-31')
ORDER BY h.id_gps, h.fh_prod
Here is my explain:
1, 'SIMPLE', 'v', 'const', 'PRIMARY,id_empresa,id_gps', 'PRIMARY', '8', 'const,const', 1, 'Using temporary; Using filesort'
1, 'SIMPLE', 'uw', 'const', 'PRIMARY', 'PRIMARY', '52', 'const', 1, ''
1, 'SIMPLE', 'h', 'ref', 'id_GPS,fh_prod', 'id_GPS', '5', 'const', 147698, 'Using where'
Also, the query hangs with the state "Copying to tmp table"
I have Windows 64bits, with 16Gb RAM and 2 QuadCores.
Any suggestion?
Thanks!