MySQL Forums
Forum List  »  Optimizer & Parser

Help with a very slow select query
Posted by: Francisco Javier Morales López de Gamarra
Date: February 02, 2011 04:45AM

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 !!

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with a very slow select query
2698
February 02, 2011 04:45AM
1253
February 02, 2011 10:24AM


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.