MySQL Forums
Forum List  »  Optimizer & Parser

Problem with a query performance
Posted by: V Pode
Date: January 04, 2012 01:43PM

We have a table with more than 800.000 records. We make several querys with diferent parameter and groups by on it. The same query with differents values on the same parameter behaves different using different indexes
Mysql is not alone selecting the best index.

For example, this query:
SELECT RANK.SITE, MAX(RANK.TIPO_EDICION) AS TIPO_EDICION, MAX(RANK.EDICION) AS EDICION, MAX(RANK.SECCION) AS SECCION, MAX(RANK.AUTOR) AS AUTOR, MAX(RANK.TIPO_CONTENIDO) AS TIPO_CONTENIDO, 0 AS CANTIDAD_HITS, 0 AS CANTIDAD_COMENTARIOS, SUM(SUMA_VALORACIONES) AS VALORACIONES, SUM(CANTIDAD_VALORACIONES) AS CANTIDAD_VALORACIONES, 0 AS CANTIDAD_RECOMENDACIONES, RANK.URL FROM TFS_STATISTICS AS RANK USE INDEX (TFS_STATISTICS_RANKING5) WHERE RANK.SITE='NewsPaper' AND RANK.SECCION='futbol' AND RANK.TIMESLOT>='2011-12-01 00:01:00' AND RANK.TIMESLOT<='2011-12-15 00:01:00' AND CANTIDAD_VALORACIONES > 0 GROUP BY RANK.SITE, RANK.URL ORDER BY VALORACIONES desc, RANK.URL LIMIT 10;

forcing the index TFS_STATISTICS_VALORACIONES it takes 3 seconds.
forcing the index TFS_STATISTICS_RANKING_SECCION_VALORACION it takes 34 seconds.
on the other side, the same query, changing the value SECCION:
SELECT RANK.SITE, MAX(RANK.TIPO_EDICION) AS TIPO_EDICION, MAX(RANK.EDICION) AS EDICION, MAX(RANK.SECCION) AS SECCION, MAX(RANK.AUTOR) AS AUTOR, MAX(RANK.TIPO_CONTENIDO) AS TIPO_CONTENIDO, 0 AS CANTIDAD_HITS, 0 AS CANTIDAD_COMENTARIOS, SUM(SUMA_VALORACIONES) AS VALORACIONES, SUM(CANTIDAD_VALORACIONES) AS CANTIDAD_VALORACIONES, 0 AS CANTIDAD_RECOMENDACIONES, RANK.URL FROM TFS_STATISTICS AS RANK USE INDEX (TFS_STATISTICS_RANKING5) WHERE RANK.SITE='NewsPaper' AND RANK.SECCION='baloncesto' AND RANK.TIMESLOT>='2011-12-01 00:01:00' AND RANK.TIMESLOT<='2011-12-15 00:01:00' AND CANTIDAD_VALORACIONES > 0 GROUP BY RANK.SITE, RANK.URL ORDER BY VALORACIONES desc, RANK.URL LIMIT 10;
forcing the index TFS_STATISTICS_VALORACIONES it takes 3 seconds.
forcing the index TFS_STATISTICS_RANKING_SECCION_VALORACION it takes 1 seconds.
This means that in a case we should use an index and in the other case the other.
One important thing to say is that 63% of the records have SECTION = 'futbol', but this may change in the future.
There is a way to make mysql select correctly the indexes, without forcing it from the code?

The table has the following structure:
TFS_STATISTICS:
URL VARCHAR(255) NOT NULL,
SITE VARCHAR(150),
TIPO_EDICION INTEGER NOT NULL,
EDICION INTEGER NOT NULL,
SECCION VARCHAR(150) NOT NULL,
TIPO_CONTENIDO INTEGER NOT NULL,
AUTOR VARCHAR(36) NOT NULL,
FECHACREACION TIMESTAMP NOT NULL,
FECHAMODIFICACION TIMESTAMP NOT NULL,
FECHAULTIMAMODIFICACION TIMESTAMP NOT NULL,
TIMESLOT TIMESTAMP NOT NULL,
CANTIDAD_HITS INTEGER NOT NULL,
CANTIDAD_VALORACIONES INTEGER NOT NULL,
SUMA_VALORACIONES INTEGER NOT NULL,
CANTIDAD_COMENTARIOS INTEGER NOT NULL,
CANTIDAD_RECOMENDACIONES INTEGER NOT NULL,
SLOT INTEGER NOT NULL,
PRIMARY KEY ( URL , SITE , TIPO_EDICION , EDICION , TIMESLOT )

and the indexes are:

TFS_STATISTICS_VALORACIONES: CANTIDAD_VALORACIONES, SITE, TIPO_EDICION, EDICION, SECCION
TFS_STATISTICS_RANKING_SECCION_VALORACION: SITE, SECCION, URL, CANTIDAD_VALORACIONES, TIMESLOT

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with a query performance
2168
January 04, 2012 01:43PM
933
January 05, 2012 10:41AM
898
January 06, 2012 12:36PM
909
January 07, 2012 10:52AM


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.