MySQL Forums
Forum List  »  Optimizer & Parser

Re: Problem with a query performance
Posted by: V Pode
Date: January 06, 2012 12:36PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2985
January 04, 2012 01:43PM
1256
January 05, 2012 10:41AM
Re: Problem with a query performance
1156
January 06, 2012 12:36PM
1188
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.