Help with a very slow select query
First, thank you very much for your help, here is my problem:
I have a clasic categories and items table, and I need to know how much items are on each category.... I have a intermediate table for make the relation between them and here here is the query that I exec:
SELECT count(articulos_en_categorias.id_articulo) as total FROM articulos, articulos_en_categorias WHERE articulos_en_categorias.id_articulo = articulos.id_articulo AND articulos.expirado = '0' AND articulos.activo = '1' AND articulos.eliminado = '0' AND articulos.id_estado_articulo > 2 AND articulos.id_estado_articulo < 5 AND articulos_en_categorias.id_categoria = '125'
My problem is that I have a lot of categories and levels of categories so I have to make a lot SELECT(count) for get the total number of items for each category ... I exec with query inside a for loop for ... about 200 categories ...
Please, any idea for increase the velocity of the query?, could I get the item number in categories using other query better??
Here are my tables definition: Thansk a million
CREATE TABLE IF NOT EXISTS `articulos_en_categorias` (
`id_articulo` int(11) default NULL,
`id_categoria` int(11) default NULL,
`url` varchar(255) default NULL,
KEY `fk_relationship_1` (`id_articulo`),
KEY `fk_relationship_20` (`id_categoria`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `categorias` (
`id_categoria` int(11) NOT NULL auto_increment,
`id_idioma` int(11) default NULL,
`id_pais` int(11) default NULL,
`id_categoria_encriptado` varchar(255) default NULL,
`nombre` varchar(64) default NULL,
`nombre_txt` varchar(64) default NULL,
`tags` text,
`relacion` varchar(64) default NULL,
`nivel` int(11) default NULL,
`padre` int(11) default NULL,
`orden` int(11) default NULL,
`activo` int(11) default NULL,
PRIMARY KEY (`id_categoria`),
KEY `fk_relationship_2` (`id_idioma`),
KEY `fk_relationship_27` (`id_pais`),
KEY `nivel` (`nivel`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=267 ;
CREATE TABLE IF NOT EXISTS `articulos` (
`id_articulo` int(11) NOT NULL auto_increment,
`id_cc` int(11) default NULL,
`id_municipio` int(11) default NULL,
`id_pago` int(11) default NULL,
`id_provincia` int(11) default NULL,
`id_usuario` int(11) default NULL,
`id_solicitud_cambio` int(11) default NULL,
`id_estado_articulo` int(11) default NULL,
`id_articulo_encriptado` varchar(255) default NULL,
`seudonimo` varchar(64) default NULL,
`titulo` varchar(45) default NULL,
`subtitulo` varchar(45) default NULL,
`url` varchar(255) default NULL,
`contenido` text,
`numero_de_imagenes` int(11) default NULL,
`imagen1` varchar(255) default NULL,
`duracion_anuncio` int(11) default NULL,
`precio_estimado` float(10,2) default NULL,
`articulo_nuevo` int(11) default NULL,
`fecha_creacion` datetime default NULL,
`fecha_ultima_actualizacion` date default NULL,
`relevancia` int(11) default NULL,
`fecha_publicacion` datetime default NULL,
`fecha_fin` datetime default NULL,
`articulo_nuevo_usado` int(11) default NULL,
`bloqueado_x_cambio` int(11) default NULL,
`ofrecido` int(11) default NULL,
`codigo_promocional` varchar(32) default NULL,
`codigo_articulo` varchar(16) default NULL,
`activo` int(11) default NULL,
`eliminado` int(11) default NULL,
`expirado` int(11) default NULL,
`destacado` int(11) default NULL,
PRIMARY KEY (`id_articulo`),
KEY `fk_relationship_21` (`id_pago`),
KEY `fk_relationship_23` (`id_usuario`),
KEY `fk_relationship_24` (`id_provincia`),
KEY `fk_relationship_25` (`id_solicitud_cambio`),
KEY `fk_relationship_34` (`id_cc`),
KEY `fk_relationship_35` (`id_municipio`),
KEY `fk_relationship_5` (`id_estado_articulo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=154 ;
-----
Thansk a lot !!