I'm trying to generate new index on a table in order to get fastest querys. My table called 'conexiones' :
CREATE TABLE `conexiones` (
`idConexion` int(10) unsigned NOT NULL AUTO_INCREMENT,
`idInstalacion` int(10) unsigned DEFAULT NULL,
`idUsuario` int(11) DEFAULT NULL,
`tMacAdres` varchar(64) DEFAULT NULL,
`tUsuario` varchar(128) DEFAULT NULL,
`tNombre` varchar(64) DEFAULT NULL,
`tApellido` varchar(64) DEFAULT NULL,
`tEmail` varchar(64) DEFAULT NULL,
`tSexo` varchar(20) DEFAULT NULL,
`fNacimiento` date DEFAULT NULL,
`nAmigos` int(11) DEFAULT NULL,
`tPoblacion` varchar(64) DEFAULT NULL,
`fFecha` datetime DEFAULT NULL,
`tEvento` varchar(20) DEFAULT NULL,
PRIMARY KEY (`idConexion`),
KEY `idInstalacion` (`idInstalacion`),
KEY `tMacAdress` (`tMacAdres`) USING BTREE,
KEY `fFecha` (`fFecha`),
KEY `idUsuario` (`idUsuario`),
KEY `insta_fecha` (`idInstalacion`,`fFecha`)
) ENGINE=InnoDB AUTO_INCREMENT=2365270 DEFAULT CHARSET=utf8;
The table has 2365270 rows.
What I don't undestand is that running that query:
select count(*) from conexiones
where conexiones.idInstalacion=190
The return value is: 59314
But if I insert EXPLAIN table the return is:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE conexiones ref idInstalacion,insta_fecha idInstalacion 5 const 108830 "Using index"
108830 rows ?
Why is searching more rows than the total rows I got from count(*) ?
Thanks !
Aucun commentaire:
Enregistrer un commentaire