MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help with a very slow select query
Posted by: Richard Buckmaster
Date: February 02, 2011 06:02AM

You really need to use modern join syntax to get the best performance. Sometimes that is all that is needed. Here is your query rewritten:

SELECT count(articulos_en_categorias.id_articulo) as total
FROM articulos
INNER JOIN articulos_en_categorias ON articulos_en_categorias.id_articulo = articulos.id_articulo
WHERE 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';

Also, if all you are trying to get a count of articles in categories you can do it with a single statement execution. Here is your query that produces a set of id_categoria values along with an associated count. (I have aliased the tables names to make the query easier to read).

SELECT AinC.id_categoria, count(*) as ArtInCat
FROM articulos Art
INNER JOIN articulos_en_categorias AinC ON AinC.id_articulo = Art.id_articulo
WHERE Art.expirado = '0'
AND Art.activo = '1'
AND Art.eliminado = '0'
AND Art.id_estado_articulo > 2
AND Art.id_estado_articulo < 5
GROUP BY AinC.id_categoria;

Finally, you need to make sure there is a non-unique index on articulos_en_categorias.id_categoria

Hope this helps.


Options: ReplyQuote

Written By
Re: Help with a very slow select query
February 02, 2011 06:02AM
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.