Re: Problem with a query performance

[V Pode]

Rick:

The create statement:

CREATE TABLE `TFS_STATISTICS` (
  `URL` varchar(255) NOT NULL,
  `SITE` varchar(50) NOT NULL,
  `TIPO_EDICION` int(11) NOT NULL,
  `EDICION` int(11) NOT NULL,
  `SECCION` varchar(50) NOT NULL,
  `TIPO_CONTENIDO` int(11) NOT NULL,
  `AUTOR` varchar(36) NOT NULL,
  `FECHACREACION` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `FECHAMODIFICACION` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `FECHAULTIMAMODIFICACION` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `TIMESLOT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `CANTIDAD_HITS` int(11) NOT NULL,
  `CANTIDAD_VALORACIONES` int(11) NOT NULL,
  `SUMA_VALORACIONES` int(11) NOT NULL,
  `CANTIDAD_COMENTARIOS` int(11) NOT NULL,
  `CANTIDAD_RECOMENDACIONES` int(11) NOT NULL,
  `SLOT` int(11) NOT NULL,
  PRIMARY KEY (`URL`,`SITE`,`TIPO_EDICION`,`EDICION`,`TIMESLOT`),
  KEY `TFS_STATISTICS_RANK` (`CANTIDAD_HITS`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`),
  KEY `TFS_STATISTICS_COMENTARIOS` (`CANTIDAD_COMENTARIOS`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`),
  KEY `TFS_STATISTICS_RECOMENDACIONES` (`CANTIDAD_RECOMENDACIONES`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`),
  KEY `TFS_STATISTICS_VALORACIONES` (`CANTIDAD_VALORACIONES`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`),
  KEY `TFS_STATISTICS_SVALORACIONES` (`SUMA_VALORACIONES`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`),
  KEY `TFS_STATISTICS_CONTENIDO` (`TIPO_CONTENIDO`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`),
  KEY `TFS_STATISTICS_AUTOR` (`AUTOR`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`),
  KEY `TFS_STATISTICS_PURGE` (`SLOT`,`TIMESLOT`),
  KEY `TFS_STATISTICS_RANKING` (`AUTOR`,`TIPO_CONTENIDO`,`SITE`,`TIPO_EDICION`,`EDICION`,`SECCION`,`URL`),
  KEY `TFS_STATISTICS_RANKING2` (`TIMESLOT`,`TIPO_CONTENIDO`),
  KEY `TFS_STATISTICS_RANKING3` (`TIMESLOT`,`SECCION`),
  KEY `TFS_STATISTICS_RANKING4` (`URL`,`TIMESLOT`),
  KEY `TFS_STATISTICS_RANKING5` (`CANTIDAD_VALORACIONES`,`SECCION`,`TIMESLOT`,`SITE`,`URL`),
  KEY `TFS_STATISTICS_RANKING6` (`CANTIDAD_HITS`,`SECCION`,`TIMESLOT`,`SITE`,`URL`),
  KEY `TFS_STATISTICS_RANKING7` (`CANTIDAD_RECOMENDACIONES`,`SECCION`,`TIMESLOT`,`SITE`,`URL`),
  KEY `TFS_STATISTICS_RANKING8` (`CANTIDAD_COMENTARIOS`,`SECCION`,`TIMESLOT`,`SITE`,`URL`),
  KEY `TFS_STATISTICS_RANKING_SECCION_VALORACION` (`SITE`,`SECCION`,`URL`,`CANTIDAD_VALORACIONES`,`TIMESLOT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The table status:

SHOW TABLE STATUS LIKE 'TFS_STATISTICS';
+----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name           | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| TFS_STATISTICS | InnoDB |      10 | Compact    | 845345 |            183 |   155189248 |               0 |   1854750720 | 2154823680 |           NULL | 2012-01-05 22:51:38 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

The query with explain

EXPLAIN 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 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;
+----+-------------+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys                                                                                                                                 | key                                       | key_len | ref         | rows   | Extra                                        |
+----+-------------+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------------+--------+----------------------------------------------+
|  1 | SIMPLE      | RANK  | ref  | TFS_STATISTICS_VALORACIONES,TFS_STATISTICS_RANKING2,TFS_STATISTICS_RANKING3,TFS_STATISTICS_RANKING5,TFS_STATISTICS_RANKING_SECCION_VALORACION | TFS_STATISTICS_RANKING_SECCION_VALORACION | 104     | const,const | 371184 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------------+--------+----------------------------------------------+

I executed the query with all the posible keys.
Each query was executed after:

RESET QUERY CACHE;
FLUSH TABLES;

The results:

With no index specified (futbol):
10 rows in set (6.34 sec)

With no index specified (baloncesto):
10 rows in set (0.21 sec)

TFS_STATISTICS_VALORACIONES (futbol):
10 rows in set (3.24 sec)

TFS_STATISTICS_VALORACIONES (baloncesto):
10 rows in set (3.01 sec)

TFS_STATISTICS_RANKING2 (futbol):
10 rows in set (16.21 sec)

TFS_STATISTICS_RANKING2 (baloncesto):
10 rows in set (15.80 sec)

TFS_STATISTICS_RANKING3 (futbol):
10 rows in set (15.35 sec)

TFS_STATISTICS_RANKING3 (baloncesto):
10 rows in set (14.98 sec)

TFS_STATISTICS_RANKING5 (futbol): << BEST
10 rows in set (3.17 sec)

TFS_STATISTICS_RANKING5 (baloncesto):
10 rows in set (3.02 sec)


TFS_STATISTICS_RANKING_SECCION_VALORACION (futbol):
10 rows in set (6.34 sec)

TFS_STATISTICS_RANKING_SECCION_VALORACION (baloncesto): << BEST
10 rows in set (0.19 sec)

TFS_STATISTICS_VALORACIONES,TFS_STATISTICS_RANKING2,TFS_STATISTICS_RANKING3,TFS_STATISTICS_RANKING5,TFS_STATISTICS_RANKING_SECCION_VALORACION (futbol):
10 rows in set (6.29 sec)

TFS_STATISTICS_VALORACIONES,TFS_STATISTICS_RANKING2,TFS_STATISTICS_RANKING3,TFS_STATISTICS_RANKING5,TFS_STATISTICS_RANKING_SECCION_VALORACION (baloncesto):
10 rows in set (0.19 sec) << BEST