mysql.txt

mysql> explain SELECT `me`.`dist`, `me`.`version`, `me`.`pass`, `me`.`fail`, `me`.`na`, `me`.`unknown` FROM `release_summary` `me`  JOIN `cpanstats` `report` ON `report`.`guid` = `me`.`guid` WHERE ( ( `patched` = 1 AND `perlmat` = 1 AND `report`.`fulldate` >= 201610250000 ) );
+----+-------------+--------+------------+------+------------------------------------------+-------------+---------+-------------------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys                            | key         | key_len | ref               | rows   | filtered | Extra                    |
+----+-------------+--------+------------+------+------------------------------------------+-------------+---------+-------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | me     | NULL       | ref  | maturity,maturity_patched,ix_join_report | maturity    | 2       | const             | 316745 |    10.00 | Using where              |
|  1 | SIMPLE      | report | NULL       | ref  | guid,ix_fulldate                         | ix_fulldate | 36      | cpanstats.me.guid |      1 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+------+------------------------------------------+-------------+---------+-------------------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.60 sec)

mysql> show create table release_summary;
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                              |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------+
| release_summary | CREATE TABLE `release_summary` (
  `dist` varchar(255) NOT NULL,
  `version` varchar(255) NOT NULL,
  `id` int(10) unsigned NOT NULL,
  `guid` char(36) NOT NULL,
  `oncpan` tinyint(4) DEFAULT '0',
  `distmat` tinyint(4) DEFAULT '0',
  `perlmat` tinyint(4) DEFAULT '0',
  `patched` tinyint(4) DEFAULT '0',
  `pass` int(10) DEFAULT '0',
  `fail` int(10) DEFAULT '0',
  `na` int(10) DEFAULT '0',
  `unknown` int(10) DEFAULT '0',
  `uploadid` int(10) DEFAULT '0',
  KEY `dist` (`dist`,`version`),
  KEY `ident` (`id`,`guid`),
  KEY `summary` (`dist`,`version`,`oncpan`,`distmat`,`perlmat`,`patched`),
  KEY `maturity` (`perlmat`),
  KEY `IXUPID` (`uploadid`),
  KEY `maturity_patched` (`perlmat`,`patched`),
  KEY `ix_join_report` (`guid`,`perlmat`,`patched`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

mysql> show create table cpanstats;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
---------+
| Table     | Create Table




         |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------$
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$---------+
| cpanstats | CREATE TABLE `cpanstats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `guid` char(36) NOT NULL DEFAULT '',
  `state` varchar(32) DEFAULT NULL,
  `postdate` varchar(8) DEFAULT NULL,
  `tester` varchar(255) DEFAULT NULL,
  `dist` varchar(255) DEFAULT NULL,
  `version` varchar(255) DEFAULT NULL,
  `platform` varchar(255) DEFAULT NULL,
  `perl` varchar(255) DEFAULT NULL,
  `osname` varchar(255) DEFAULT NULL,
  `osvers` varchar(255) DEFAULT NULL,
  `fulldate` varchar(32) DEFAULT NULL,
  `type` int(2) DEFAULT '0',
  `uploadid` int(10) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `guid` (`guid`),
  KEY `distvers` (`dist`,`version`),
  KEY `tester` (`tester`),
  KEY `state` (`state`),
  KEY `postdate` (`postdate`),
  KEY `IXUPID` (`uploadid`),
  KEY `cpanstats_dist_state` (`dist`,`state`),
  KEY `ix_fulldate` (`guid`,`fulldate`)
) ENGINE=InnoDB AUTO_INCREMENT=75273216 DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------+
1 row in set (0.09 sec)

subselect.txt

mysql> explain SELECT `dist`, `version`, `pass`, `fail`, `na`, `unknown` FROM ( SELECT `me`.`dist`, `me`.`version`, `me`.`pass`, `me`.`fail`, `me`.`na`, `me`.`unknown`, `me`.`perlmat`, `me`.`patched`, `report`.`fulldate` FROM `release_summary` `me`  JOIN `cpanstats` `report` ON `report`.`guid` = `me`.`guid` ) AS `derived` WHERE `perlmat` = 1 AND `patched` = 1 AND `fulldate` >= 201610250000;
+----+-------------+--------+------------+------+----------------------------------------------------------+-------------+---------+-------------------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys                                            | key         | key_len | ref               | rows   | filtered | Extra                    |
+----+-------------+--------+------------+------+----------------------------------------------------------+-------------+---------+-------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | me     | NULL       | ref  | maturity,maturity_patched,ix_join_report,ix_release_guid | maturity    | 2       | const             | 316745 |    10.00 | Using where              |
|  1 | SIMPLE      | report | NULL       | ref  | guid,ix_fulldate                                         | ix_fulldate | 36      | cpanstats.me.guid |      1 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+------+----------------------------------------------------------+-------------+---------+-------------------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.11 sec)