lundi 20 juin 2016

Select count(*) different from exapain rows

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