why not using index
Posted by: Raul Carlomagno
Date: December 17, 2004 02:53PM
Date: December 17, 2004 02:53PM
im using mysql 4.1.7 and i have these tables:
Texts (584949 rows)
Properties (543237 rows)
Classifieds (1269543 rows)
Properties_zones (634104 rows)
Zones (331 rows)
tables ddl:
--------------------
CREATE TABLE `Texts` (
`Text_id` int(11) NOT NULL auto_increment,
`Inmobiliaria_id` int(11) default NULL,
`Original` blob,
`Traducido` blob,
PRIMARY KEY (`Text_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
---------------------
CREATE TABLE `Properties` (
`Propertie_id` int(11) NOT NULL auto_increment,
`Text_id` int(11) default NULL,
`Tipo_Inmueble` varchar(10) collate latin1_spanish_ci default NULL,
`Tipo_Operacion` varchar(6) collate latin1_spanish_ci default NULL,
`Superficie` double default NULL,
`Moneda` char(1) collate latin1_spanish_ci default NULL,
`Valuacion` double default NULL,
`Tipo_Atributo` varchar(10) collate latin1_spanish_ci default NULL,
`Ambientes` varchar(10) collate latin1_spanish_ci default NULL,
`Marca` tinyint(4) default NULL,
PRIMARY KEY (`Propertie_id`),
KEY `Text_id` (`Text_id`),
KEY `Tipo_Inmueble` (`Tipo_Inmueble`),
CONSTRAINT `Propiedades_ibfk_1` FOREIGN KEY (`Text_id`) REFERENCES `Texts` (`Text_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
---------------------
CREATE TABLE `Classifieds` (
`Classified_Id` int(11) NOT NULL auto_increment,
`Text_id` int(11) default NULL,
`Medio_id` int(11) default NULL,
`classif_date` date default NULL,
PRIMARY KEY (`Avisos_Id`),
KEY `Text_id` (`Text_id`),
KEY `Medio_id` (`Medio_id`),
KEY `clasiff_date` (`clasiff_date`),
CONSTRAINT `Avisos_ibfk_1` FOREIGN KEY (`Text_id`) REFERENCES `Texts` (`Text_id`),
CONSTRAINT `Avisos_ibfk_2` FOREIGN KEY (`Medio_id`) REFERENCES `Medios` (`Medio_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
-----------------------
CREATE TABLE `Properties_zones` (
`PropertieZone_id` int(11) default NULL,
`Propertie_id` int(11) NOT NULL default '0',
`Zone_id` int(11) NOT NULL default '0',
`Mtch` smallint(5) default NULL,
PRIMARY KEY (`Propertie_id`,`Zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
-----------------------
CREATE TABLE `Zones` (
`Zone_id` int(11) NOT NULL auto_increment,
`Name` varchar(37) collate latin1_spanish_ci default NULL,
`Localidad_id` int(11) default NULL,
`TipoZona_id` smallint(5) default NULL,
PRIMARY KEY (`Zone_id`),
KEY `Localidad_id` (`Localidad_id`),
KEY `TipoZona_id` (`TipoZona_id`),
CONSTRAINT `Zonas_ibfk_1` FOREIGN KEY (`Localidad_id`) REFERENCES `Localidades` (`Localidad_id`),
CONSTRAINT `Zonas_ibfk_2` FOREIGN KEY (`TipoZona_id`) REFERENCES `TipoZona` (`TipoZona_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
----------------------
here is the problem:
when i do:
explain select count(0) from Classifieds where classif_date between '2004-06-01' and '2004-06-15'
it gives me:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a range classif_date classif_date 4 (NULL) 155104 Using where; Using index
it does use the index 'classif_date'
but when i do:
explain select count(t.texxt_id) from Texts t inner join Properties p on p.text_id = t.text_id
INNER join Classifieds a on a.text_id = t.text_id
inner join Properties_zones pz on p.propertie_id = pz.propertie_id
inner join Zones z on pz.zone_id = z.zone_id
where
z.zone_id = 23 and a.classif_date between '2004-06-01' and '2004-06-15'
it gives me:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE z const PRIMARY PRIMARY 4 const 1 Using index
1 SIMPLE a range Text_id,classif_date classif_date 4 (NULL) 155104 Using where
1 SIMPLE t eq_ref PRIMARY PRIMARY 4 MYDB.a.Text_id 1 Using index
1 SIMPLE p ref PRIMARY,Text_id Text_id 5 MYDB.t.Text_id 1 Using where; Using index
1 SIMPLE pz eq_ref PRIMARY PRIMARY 8 MYDB.p.Propertie_id,const 1 Using index
the table Classifieds is not using the classifdate index, so mysql has to read all table, why mysql is not using it?
Texts (584949 rows)
Properties (543237 rows)
Classifieds (1269543 rows)
Properties_zones (634104 rows)
Zones (331 rows)
tables ddl:
--------------------
CREATE TABLE `Texts` (
`Text_id` int(11) NOT NULL auto_increment,
`Inmobiliaria_id` int(11) default NULL,
`Original` blob,
`Traducido` blob,
PRIMARY KEY (`Text_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
---------------------
CREATE TABLE `Properties` (
`Propertie_id` int(11) NOT NULL auto_increment,
`Text_id` int(11) default NULL,
`Tipo_Inmueble` varchar(10) collate latin1_spanish_ci default NULL,
`Tipo_Operacion` varchar(6) collate latin1_spanish_ci default NULL,
`Superficie` double default NULL,
`Moneda` char(1) collate latin1_spanish_ci default NULL,
`Valuacion` double default NULL,
`Tipo_Atributo` varchar(10) collate latin1_spanish_ci default NULL,
`Ambientes` varchar(10) collate latin1_spanish_ci default NULL,
`Marca` tinyint(4) default NULL,
PRIMARY KEY (`Propertie_id`),
KEY `Text_id` (`Text_id`),
KEY `Tipo_Inmueble` (`Tipo_Inmueble`),
CONSTRAINT `Propiedades_ibfk_1` FOREIGN KEY (`Text_id`) REFERENCES `Texts` (`Text_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
---------------------
CREATE TABLE `Classifieds` (
`Classified_Id` int(11) NOT NULL auto_increment,
`Text_id` int(11) default NULL,
`Medio_id` int(11) default NULL,
`classif_date` date default NULL,
PRIMARY KEY (`Avisos_Id`),
KEY `Text_id` (`Text_id`),
KEY `Medio_id` (`Medio_id`),
KEY `clasiff_date` (`clasiff_date`),
CONSTRAINT `Avisos_ibfk_1` FOREIGN KEY (`Text_id`) REFERENCES `Texts` (`Text_id`),
CONSTRAINT `Avisos_ibfk_2` FOREIGN KEY (`Medio_id`) REFERENCES `Medios` (`Medio_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
-----------------------
CREATE TABLE `Properties_zones` (
`PropertieZone_id` int(11) default NULL,
`Propertie_id` int(11) NOT NULL default '0',
`Zone_id` int(11) NOT NULL default '0',
`Mtch` smallint(5) default NULL,
PRIMARY KEY (`Propertie_id`,`Zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
-----------------------
CREATE TABLE `Zones` (
`Zone_id` int(11) NOT NULL auto_increment,
`Name` varchar(37) collate latin1_spanish_ci default NULL,
`Localidad_id` int(11) default NULL,
`TipoZona_id` smallint(5) default NULL,
PRIMARY KEY (`Zone_id`),
KEY `Localidad_id` (`Localidad_id`),
KEY `TipoZona_id` (`TipoZona_id`),
CONSTRAINT `Zonas_ibfk_1` FOREIGN KEY (`Localidad_id`) REFERENCES `Localidades` (`Localidad_id`),
CONSTRAINT `Zonas_ibfk_2` FOREIGN KEY (`TipoZona_id`) REFERENCES `TipoZona` (`TipoZona_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
----------------------
here is the problem:
when i do:
explain select count(0) from Classifieds where classif_date between '2004-06-01' and '2004-06-15'
it gives me:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a range classif_date classif_date 4 (NULL) 155104 Using where; Using index
it does use the index 'classif_date'
but when i do:
explain select count(t.texxt_id) from Texts t inner join Properties p on p.text_id = t.text_id
INNER join Classifieds a on a.text_id = t.text_id
inner join Properties_zones pz on p.propertie_id = pz.propertie_id
inner join Zones z on pz.zone_id = z.zone_id
where
z.zone_id = 23 and a.classif_date between '2004-06-01' and '2004-06-15'
it gives me:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE z const PRIMARY PRIMARY 4 const 1 Using index
1 SIMPLE a range Text_id,classif_date classif_date 4 (NULL) 155104 Using where
1 SIMPLE t eq_ref PRIMARY PRIMARY 4 MYDB.a.Text_id 1 Using index
1 SIMPLE p ref PRIMARY,Text_id Text_id 5 MYDB.t.Text_id 1 Using where; Using index
1 SIMPLE pz eq_ref PRIMARY PRIMARY 8 MYDB.p.Propertie_id,const 1 Using index
the table Classifieds is not using the classifdate index, so mysql has to read all table, why mysql is not using it?
Subject
Views
Written By
Posted
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.