Ygthkb

MySQL 5.5.49-log

More questions on the query in Why does it use temporary? (MySQL) (the query is the same but the question is different):

I have the following table (filled with many rows):

CREATE TABLE `SectorGraphs2` (

  `Kind` tinyint(3) UNSIGNED NOT NULL COMMENT '1 - продюсер, 2 - жанр, 3 - регион',

  `Criterion` tinyint(3) UNSIGNED NOT NULL,

  `Period` tinyint(3) UNSIGNED NOT NULL,

  `PeriodStart` date NOT NULL,

  `SectorID` int(10) UNSIGNED NOT NULL,

  `Value` float NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



ALTER TABLE `SectorGraphs2`

  ADD UNIQUE KEY `Producer2` (`Kind`,`Criterion`,`Period`,`PeriodStart`,`SectorID`) USING BTREE,

  ADD KEY `SectorID` (`SectorID`);

then I run:

EXPLAIN 

    SELECT SectorID, SUM(Value)

    FROM SectorGraphs2

    WHERE Kind = 1 AND Criterion = 7

      AND Period = 1

      AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK

    GROUP BY SectorID

and it produces:

+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+

| id | select_type | table         | type  | possible_keys | key       | key_len | ref  | rows | extra                                        |

+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+

|  1 | SIMPLE      | SectorGraphs2 | range | Producer2     | Producer2 | 6       | NULL | 1    | Using where; Using temporary; Using filesort |

+----+-------------+---------------+-------+---------------+-----------+---------+------+------+----------------------------------------------+

See a nicely formatted explanation here.

My question: Why it is used a temporary table and filesort but it reports only 1 row examined? It seems that because of using a temporary table, it should process more than one row. How can I determine the real number of rows processed? How to solve this discrepancy about number of processed rows?

Note that the task I was assigned to do now is to eliminate heavy (involving too many rows) queries. And now I do not know how to do this.

Try to replace AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK clause with precalculated version:

SET @PeriodEnd = ? + INTERVAL 1 WEEK;

SELECT SectorID, SUM(Value)

    FROM SectorGraphs2

    WHERE Kind = 1 AND Criterion = 7

      AND Period = 1

      AND PeriodStart BETWEEN ? AND @PeriodEnd

    GROUP BY SectorID;

Explicitly point the index you want to use:

SET @PeriodEnd = ? + INTERVAL 1 WEEK;

SELECT SectorID, SUM(Value)

    FROM SectorGraphs2 USE INDEX (`Producer2`)

    WHERE Kind = 1 AND Criterion = 7

      AND Period = 1

      AND PeriodStart BETWEEN ? AND @PeriodEnd

    GROUP BY SectorID;

Reorder fields in the index definition placing more selective fields first. To determine selectivity run the next query:

SELECT COUNT( DISTINCT Criterion )

  FROM SectorGraphs2;

Then replace Criterion with Kind, PeriodStart, SectorID etc.

The higher count means better selectivity and you have to arrange fields in the index from the best selectivity to worst.