Create a gist now

Instantly share code, notes, and snippets.

@preaction /mysql.txt
Last active Nov 20, 2016

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)
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment