MySQL Forums
Forum List  »  Performance

Slow queries with large table
Posted by: Pablo Garin
Date: December 02, 2013 08:09AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow queries with large table
5278
December 02, 2013 08:09AM
1876
December 03, 2013 09:29PM
1512
December 04, 2013 05:22AM
1379
December 05, 2013 12:05PM
1304
December 05, 2013 02:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.