Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.1.73, 5.2.15, 5.3.13, 5.5(EOL), 10.0(EOL)
-
centos 6 x86_64
-
5.5.44
Description
I have a 5.5.29 and 5.5.42 with the same data. 5.5.42 was just created from the 5.5.29 instance.
The obs query plan changes the result time significantly.
5.5.29 uses the query:
explain SELECT obsloc.sScreenName AS ObsLocation, obsloc.sScreenName AS SiteName, obsloc.sState AS SiteState, obsloc.sDisplayState AS SiteDisplayState, obsloc.nLat AS SiteLat, obsloc.nLong AS SiteLong, obsloc.iElevation AS SiteElevation, DATE_FORMAT( obs.dtDate, '%Y-%m-%d' ) AS ObsDate, climate.nMeanMinTemp FROM locations obsloc LEFT JOIN obs_daily obs ON obs.sLocType = obsloc.sType AND obs.dtDate >= '2015-03-14' AND obs.dtDate <= '2015-03-14' LEFT JOIN climatology climate ON climate.iMonth=MONTH(obs.dtDate) ORDER BY obs.dtDate limit 300;
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+-----------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+-----------------------------------------------------------------------------------------+
|
| 1 | SIMPLE | obsloc | ALL | NULL | NULL | NULL | NULL | 121555 | Using temporary; Using filesort |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | climate | ref | idx_climatology_3 | idx_climatology_3 | 4 | func | 2117 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
|
5.5.42 on the same database and query has the plan
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
|
| 1 | SIMPLE | obsloc | ALL | NULL | NULL | NULL | NULL | 102188 | Using temporary; Using filesort |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Range checked for each record (index map: 0x6); Rowid-ordered scan |
|
| 1 | SIMPLE | climate | ref | idx_climatology_3 | idx_climatology_3 | 4 | func | 5041 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
|
+------+-------------+---------+-------+---------------------------------+-------------------+---------+------+--------+----------------------------------------------------------------------------------+
|
On a significantly larger query this is the difference between
5.5.29
SELECT ...
|
FROM locations loc
|
INNER JOIN location_time_zones tz
|
ON tz.sLocType = loc.sType
|
AND tz.sLocCode = loc.sCode
|
LEFT OUTER JOIN locations c
|
ON c.sType = 'CCODE'
|
AND c.sCode = loc.sCountryCode
|
INNER JOIN location_mappings_2008 xsite
|
ON xsite.custom_code = 'TWC'
|
AND xsite.type1 = loc.sType
|
AND xsite.code1 = loc.sCode
|
AND xsite.context = 'obsdaily'
|
LEFT OUTER JOIN locations obsloc
|
ON obsloc.sType = xsite.type2
|
AND obsloc.sCode = xsite.code2
|
LEFT OUTER JOIN obs_daily obs
|
ON obs.sLocType = obsloc.sType
|
AND obs.dtDate >= '2015-03-14'
|
AND obs.dtDate <= '2015-03-14'
|
LEFT OUTER JOIN climatology climate
|
ON climate.sLocType = xsite.type2
|
AND climate.sLocCode = xsite.code2
|
AND climate.iMonth = MONTH( obs.dtDate )
|
WHERE loc.sType = 'TWCID'
|
AND loc.sCode = '9764'
|
ORDER BY obs.dtDate;
|
 |
running the query on 5.5.29
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
| 1 | SIMPLE | loc | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | Using temporary; Using filesort |
|
| 1 | SIMPLE | tz | const | idx_location_time_zones_1 | idx_location_time_zones_1 | 44 | const,const | 1 | Using index |
|
| 1 | SIMPLE | c | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | |
|
| 1 | SIMPLE | xsite | ref | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128 | const,const,const,const | 1 | Using where; Using index |
|
| 1 | SIMPLE | obsloc | eq_ref | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | weather.xsite.type2,weather.xsite.code2 | 1 | Using where |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3 | idx_climatology_1 | 48 | weather.xsite.type2,weather.xsite.code2,func | 1 | Using where |
|
 |
returns 2009 rows in set (0.10 sec)
|
 |
where as 5.5.42:
|
 |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
| 1 | SIMPLE | loc | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | Using temporary; Using filesort |
|
| 1 | SIMPLE | tz | const | idx_location_time_zones_1 | idx_location_time_zones_1 | 44 | const,const | 1 | Using index |
|
| 1 | SIMPLE | c | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | |
|
| 1 | SIMPLE | xsite | ref | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128 | const,const,const,const | 1 | Using where; Using index |
|
| 1 | SIMPLE | obsloc | eq_ref | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | weather.xsite.type2,weather.xsite.code2 | 1 | Using where |
|
| 1 | SIMPLE | obs | range | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | NULL | 5989 | Range checked for each record (index map: 0x6) |
|
| 1 | SIMPLE | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3 | idx_climatology_1 | 48 | weather.xsite.type2,weather.xsite.code2,func | 1 | Using where |
|
 |
returned 2009 rows in set (11 min 10.44 sec)
|
changing the range part on obs.dtDate to obs.dtDate = '2015-03-14'
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
| 1 | SIMPLE | loc | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | Using temporary; Using filesort |
|
| 1 | SIMPLE | tz | const | idx_location_time_zones_1 | idx_location_time_zones_1 | 44 | const,const | 1 | Using index |
|
| 1 | SIMPLE | c | const | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | const,const | 1 | |
|
| 1 | SIMPLE | xsite | ref | idx_location_mappings_2008_1,idx_location_mappings_2008_2 | idx_location_mappings_2008_1 | 128 | const,const,const,const | 1 | Using where; Using index |
|
| 1 | SIMPLE | obsloc | eq_ref | idx_locations_1,idx_locations_4 | idx_locations_1 | 44 | weather.xsite.type2,weather.xsite.code2 | 1 | Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan |
|
| 1 | SIMPLE | obs | ref | idx_obs_daily_1,idx_obs_daily_2 | idx_obs_daily_2 | 3 | const | 5989 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
|
| 1 | SIMPLE | climate | eq_ref | idx_climatology_1,idx_climatology_2,idx_climatology_3 | idx_climatology_1 | 48 | weather.xsite.type2,weather.xsite.code2,func | 1 | Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan |
|
 |
resulting in 2009 rows in set (0.14 sec)
|
index stats which don't change much after running analyize tables a few times.
5.5.29
|
dba@localhost [weather] select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages |
|
+--------------+------------+-----------------+--------+------------------+-------------------+------------------+
|
| weather | obs_daily | PRIMARY | 1 | 1 | 290176 | 253471 |
|
| weather | obs_daily | idx_obs_daily_1 | 3 | 3034775, 3513, 1 | 71232 | 62121 |
|
| weather | obs_daily | idx_obs_daily_2 | 2 | 8479, 1 | 29504 | 25696 |
|
5.5.42
|
MariaDB [weather]> select * from information_schema.INNODB_INDEX_STATS where table_schema='weather' and table_name='obs_daily';
|
+--------------+------------+-----------------+--------+-----------------+-------------------+------------------+
|
| table_schema | table_name | index_name | fields | rows_per_key | index_total_pages | index_leaf_pages |
|
+--------------+------------+-----------------+--------+-----------------+-------------------+------------------+
|
| weather | obs_daily | PRIMARY | 1 | 1 | 315136 | 275181 |
|
| weather | obs_daily | idx_obs_daily_1 | 3 | 752141, 1374, 1 | 71232 | 62106 |
|
| weather | obs_daily | idx_obs_daily_2 | 2 | 4641, 0 | 33344 | 29082 |
|
| obs_daily | CREATE TABLE `obs_daily` (
|
`iId` int(11) NOT NULL AUTO_INCREMENT,
|
`sLocType` varchar(10) NOT NULL DEFAULT '',
|
`sLocCode` varchar(30) NOT NULL DEFAULT '',
|
`dtDate` date NOT NULL DEFAULT '0000-00-00',
|
...
|
PRIMARY KEY (`iId`),
|
UNIQUE KEY `idx_obs_daily_1` (`sLocType`,`sLocCode`,`dtDate`),
|
KEY `idx_obs_daily_2` (`dtDate`)
|
) ENGINE=InnoDB AUTO_INCREMENT=24862119 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |
|
