MDEV-4750 follow-up: Reduce disabling innodb_stats_persistent

This essentially reverts commit 4e89ec6692786bc1cbdce64d43d8e85a5d247dab
and only disables InnoDB persistent statistics for tests where it is
desirable. By design, InnoDB persistent statistics will not be updated
except by ANALYZE TABLE or by STATS_AUTO_RECALC.

The internal transactions that update persistent InnoDB statistics
in background tasks (with innodb_stats_auto_recalc=ON) may cause
nondeterministic query plans or interfere with some tests that deal
with other InnoDB internals, such as the purge of transaction history.

7 files changed, 55 insertions, 50 deletions

diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result
index 26fdf39baf3..f8db53a4ca9 100644
--- a/mysql-test/suite/innodb_gis/r/1.result
+++ b/mysql-test/suite/innodb_gis/r/1.result
@@ -227,7 +227,7 @@ fid	ST_AsText(ST_Envelope(g))
 121	POLYGON((3 6,44 6,44 9,3 9,3 6))
 explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_geometry	ALL	NULL	NULL	NULL	NULL	21	#	
+1	SIMPLE	gis_geometry	ALL	NULL	NULL	NULL	NULL	#	#	
 Warnings:
 Note	1003	select st_dimension(`test`.`gis_geometry`.`g`) AS `ST_Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `ST_GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `ST_IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `ST_AsText(ST_Envelope(g))` from `test`.`gis_geometry`
 SELECT fid, ST_X(g) FROM gis_point;
@@ -244,7 +244,7 @@ fid	ST_Y(g)
 104	20
 explain extended select ST_X(g),ST_Y(g) FROM gis_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	4	#	
+1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	#	#	
 Warnings:
 Note	1003	select st_x(`test`.`gis_point`.`g`) AS `ST_X(g)`,st_y(`test`.`gis_point`.`g`) AS `ST_Y(g)` from `test`.`gis_point`
 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
@@ -279,7 +279,7 @@ fid	ST_IsClosed(g)
 107	0
 explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_line	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_line	ALL	NULL	NULL	NULL	NULL	#	#	
 Warnings:
 Note	1003	select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `ST_Length(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `ST_NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `ST_AsText(ST_PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `ST_IsClosed(g)` from `test`.`gis_line`
 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
@@ -309,7 +309,7 @@ fid	ST_AsText(ST_InteriorRingN(g, 1))
 110	NULL
 explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_polygon	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_polygon	ALL	NULL	NULL	NULL	NULL	#	#	
 Warnings:
 Note	1003	select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `ST_Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `ST_NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `ST_AsText(ST_InteriorRingN(g, 1))` from `test`.`gis_polygon`
 SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
@@ -348,7 +348,7 @@ fid	ST_NumGeometries(g)
 121	2
 explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	#	#	
 Warnings:
 Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `ST_NumGeometries(g)` from `test`.`gis_multi_point`
 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
@@ -376,7 +376,7 @@ fid	ST_AsText(ST_GeometryN(g, 1))
 121	POINT(44 6)
 explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	#	#	
 Warnings:
 Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `ST_AsText(ST_GeometryN(g, 2))` from `test`.`gis_multi_point`
 SELECT g1.fid as first, g2.fid as second,
@@ -395,8 +395,8 @@ MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g)
 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	#	Using temporary; Using filesort
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	#	Using join buffer (flat, BNL join)
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	#	#	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	#	#	Using join buffer (flat, BNL join)
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
diff --git a/mysql-test/suite/innodb_gis/r/geometry.result b/mysql-test/suite/innodb_gis/r/geometry.result
index 70e0f04cdf1..437ff23e334 100644
--- a/mysql-test/suite/innodb_gis/r/geometry.result
+++ b/mysql-test/suite/innodb_gis/r/geometry.result
@@ -1,5 +1,7 @@
 SET default_storage_engine=InnoDB;
 SET innodb_strict_mode=OFF;
+SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
 CREATE TABLE gis_point  (fid INTEGER NOT NULL PRIMARY KEY, g POINT) ENGINE=InnoDB;
 CREATE TABLE gis_line  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING) ENGINE=InnoDB;
 CREATE TABLE gis_polygon   (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON) ENGINE=InnoDB;
@@ -179,39 +181,39 @@ CREATE UNIQUE INDEX idx2 on tab(c8(5) ASC) ;
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab	ref	idx1	idx1	8	const	1	#
+1	SIMPLE	tab	ref	idx1	idx1	8	const	#	Using where
 #check index with  WKB function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
 FROM tab WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab	ref	idx1	idx1	8	const	1	#
+1	SIMPLE	tab	ref	idx1	idx1	8	const	#	Using where
 #check index with  WKT function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab	ref	idx3	idx3	8	const	1	#
+1	SIMPLE	tab	ref	idx3	idx3	8	const	#	Using where
 #check index with  WKB function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
 FROM tab
 WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab	ref	idx3	idx3	8	const	1	#
+1	SIMPLE	tab	ref	idx3	idx3	8	const	#	Using where
 #check index with  WKT function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab	const	idx2	idx2	8	const	1	#
+1	SIMPLE	tab	const	idx2	idx2	8	const	#	
 #check index with  WKB function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
 FROM tab
 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab	const	idx2	idx2	8	const	1	#
+1	SIMPLE	tab	const	idx2	idx2	8	const	#	
 #check index with DELETE operation
 EXPLAIN DELETE FROM tab
 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab	range	idx2	idx2	8	NULL	1	#
+1	SIMPLE	tab	range	idx2	idx2	8	NULL	#	Using where
 #check the spatial values
 SELECT ST_AsText(c1) FROM tab;
 ST_AsText(c1)
@@ -703,39 +705,39 @@ CREATE UNIQUE INDEX idx2 on tab3(c8(5) ASC) ;
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
 WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	ref	idx1	idx1	8	const	1	#
+1	SIMPLE	tab3	ref	idx1	idx1	8	const	#	Using where
 #check index with  WKB function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	ref	idx1	idx1	8	const	1	#
+1	SIMPLE	tab3	ref	idx1	idx1	8	const	#	Using where
 #check index with  WKT function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	ref	idx3	idx3	8	const	1	#
+1	SIMPLE	tab3	ref	idx3	idx3	8	const	#	Using where
 #check index with  WKB function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
 FROM tab3
 WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	ref	idx3	idx3	8	const	1	#
+1	SIMPLE	tab3	ref	idx3	idx3	8	const	#	Using where
 #check index with  WKT function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
 WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	const	idx2	idx2	8	const	1	#
+1	SIMPLE	tab3	const	idx2	idx2	8	const	#	
 #check index with  WKB function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
 FROM tab3
 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	const	idx2	idx2	8	const	1	#
+1	SIMPLE	tab3	const	idx2	idx2	8	const	#	
 #check index with DELETE operation
 EXPLAIN DELETE FROM tab3
 WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	range	idx2	idx2	8	NULL	1	#
+1	SIMPLE	tab3	range	idx2	idx2	8	NULL	#	Using where
 #check the spatial values
 SELECT ST_AsText(c1) FROM tab3;
 ST_AsText(c1)
@@ -783,12 +785,12 @@ tab3	CREATE TABLE `tab3` (
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
 FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	ref	idx1	idx1	8	const	1	#
+1	SIMPLE	tab3	ref	idx1	idx1	8	const	#	Using where
 #check index with  WKT function
 EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
 WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tab3	ref	idx3	idx3	8	const	1	#
+1	SIMPLE	tab3	ref	idx3	idx3	8	const	#	Using where
 #check the Geometry property functions
 SELECT fid, ST_Dimension(g) FROM gis_geometry;
 fid	ST_Dimension(g)
@@ -888,7 +890,7 @@ fid	ST_AsText(ST_Envelope(g))
 122	POLYGON((3 6,44 6,44 9,3 9,3 6))
 explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_geometry	ALL	NULL	NULL	NULL	NULL	22	#	
+1	SIMPLE	gis_geometry	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_dimension(`test`.`gis_geometry`.`g`) AS `ST_Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `ST_GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `ST_IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `ST_AsText(ST_Envelope(g))` from `test`.`gis_geometry`
 #check Geometry point functions
@@ -906,7 +908,7 @@ fid	ST_Y(g)
 104	20
 explain extended select ST_X(g),ST_Y(g) FROM gis_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	4	#	
+1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_x(`test`.`gis_point`.`g`) AS `ST_X(g)`,st_y(`test`.`gis_point`.`g`) AS `ST_Y(g)` from `test`.`gis_point`
 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
@@ -942,7 +944,7 @@ fid	ST_IsClosed(g)
 explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),
 ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_line	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_line	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `ST_Length(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `ST_NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `ST_AsText(ST_PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `ST_IsClosed(g)` from `test`.`gis_line`
 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
@@ -973,7 +975,7 @@ fid	ST_AsText(ST_InteriorRingN(g, 1))
 explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),
 ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_polygon	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_polygon	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `ST_Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `ST_NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `ST_AsText(ST_InteriorRingN(g, 1))` from `test`.`gis_polygon`
 SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
@@ -1013,7 +1015,7 @@ fid	ST_NumGeometries(g)
 122	2
 explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `ST_NumGeometries(g)` from `test`.`gis_multi_point`
 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
@@ -1043,7 +1045,7 @@ fid	ST_AsText(ST_GeometryN(g, 1))
 122	POINT(44 6)
 explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `ST_AsText(ST_GeometryN(g, 2))` from `test`.`gis_multi_point`
 SELECT g1.fid as first, g2.fid as second,
@@ -1067,8 +1069,8 @@ MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g)
 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	3	#	Using temporary; Using filesort
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	3	#	Using join buffer (flat, BNL join)
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	#	100.00	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	#	100.00	Using join buffer (flat, BNL join)
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 # check support of Foreign Key constraint
@@ -1113,3 +1115,4 @@ DROP TABLE tab,tab2,tab3,parent;
 DROP PROCEDURE geominout;
 DROP PROCEDURE geom_insert;
 DROP PROCEDURE geom_cursor;
+SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
diff --git a/mysql-test/suite/innodb_gis/r/gis.result b/mysql-test/suite/innodb_gis/r/gis.result
index e673d92f5c6..f41fcab5bbb 100644
--- a/mysql-test/suite/innodb_gis/r/gis.result
+++ b/mysql-test/suite/innodb_gis/r/gis.result
@@ -1,5 +1,6 @@
 SET default_storage_engine=InnoDB;
-DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
+SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
 CREATE TABLE gis_point  (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
 CREATE TABLE gis_line  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
 CREATE TABLE gis_polygon   (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
@@ -227,7 +228,7 @@ fid	ST_AsText(ST_Envelope(g))
 121	POLYGON((3 6,44 6,44 9,3 9,3 6))
 explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_geometry	ALL	NULL	NULL	NULL	NULL	21	#	
+1	SIMPLE	gis_geometry	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_dimension(`test`.`gis_geometry`.`g`) AS `ST_Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `ST_GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `ST_IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `ST_AsText(ST_Envelope(g))` from `test`.`gis_geometry`
 SELECT fid, ST_X(g) FROM gis_point;
@@ -244,7 +245,7 @@ fid	ST_Y(g)
 104	20
 explain extended select ST_X(g),ST_Y(g) FROM gis_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	4	#	
+1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_x(`test`.`gis_point`.`g`) AS `ST_X(g)`,st_y(`test`.`gis_point`.`g`) AS `ST_Y(g)` from `test`.`gis_point`
 SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
@@ -279,7 +280,7 @@ fid	ST_IsClosed(g)
 107	0
 explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_line	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_line	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `ST_AsText(ST_EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `ST_Length(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `ST_NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `ST_AsText(ST_PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `ST_IsClosed(g)` from `test`.`gis_line`
 SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
@@ -309,7 +310,7 @@ fid	ST_AsText(ST_InteriorRingN(g, 1))
 110	NULL
 explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_polygon	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_polygon	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `ST_Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `ST_AsText(ST_ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `ST_NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `ST_AsText(ST_InteriorRingN(g, 1))` from `test`.`gis_polygon`
 SELECT fid, ST_IsClosed(g) FROM gis_multi_line;
@@ -348,7 +349,7 @@ fid	ST_NumGeometries(g)
 121	2
 explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `ST_NumGeometries(g)` from `test`.`gis_multi_point`
 SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
@@ -376,7 +377,7 @@ fid	ST_AsText(ST_GeometryN(g, 1))
 121	POINT(44 6)
 explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	3	#	
+1	SIMPLE	gis_multi_point	ALL	NULL	NULL	NULL	NULL	#	100.00	
 Warnings:
 Note	1003	select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `ST_AsText(ST_GeometryN(g, 2))` from `test`.`gis_multi_point`
 SELECT g1.fid as first, g2.fid as second,
@@ -395,8 +396,8 @@ MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g)
 MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	#	Using temporary; Using filesort
-1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	#	Using join buffer (flat, BNL join)
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	#	100.00	Using temporary; Using filesort
+1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	#	100.00	Using join buffer (flat, BNL join)
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`
 DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
@@ -1497,3 +1498,4 @@ COUNT(*)
 1
 DROP TABLE t1;
 DROP DATABASE gis_ogs;
+SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
diff --git a/mysql-test/suite/innodb_gis/r/point_basic.result b/mysql-test/suite/innodb_gis/r/point_basic.result
index 4842c72d99c..14f07d4facf 100644
--- a/mysql-test/suite/innodb_gis/r/point_basic.result
+++ b/mysql-test/suite/innodb_gis/r/point_basic.result
@@ -25,7 +25,7 @@ INSERT INTO gis_point VALUES
 (ST_PointFromText('POINT(100.32374832 101.23741821)'), ST_PointFromText('POINT(100.32374832 101.98527111)'));
 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	4	#
+1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	#	Using temporary; Using filesort
 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
 ST_AsText(p1)	ST_AsText(p2)
 POINT(100.32374832 101.23741821)	POINT(100.32374832 101.98527111)
@@ -277,7 +277,7 @@ DROP TABLE t1;
 #
 # Test when the POINT is on B-TREE
 #
-CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB;
+CREATE TABLE gis_point(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB STATS_PERSISTENT=0;
 INSERT INTO gis_point VALUES
 (101, ST_PointFromText('POINT(10 10)')),
 (102, ST_PointFromText('POINT(20 10)')),
@@ -302,7 +302,7 @@ INSERT INTO gis_point VALUES
 'The ORDER BY will use filesort'
 EXPLAIN SELECT ST_AsText(p) FROM gis_point ORDER BY p;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	10	#
+1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	#	Using filesort
 SELECT ST_AsText(p) FROM gis_point ORDER BY p;
 ST_AsText(p)
 POINT(10 10)
@@ -1326,7 +1326,7 @@ test.gis_point	check	status	OK
 The ORDER BY for spatial index will use filesort
 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	13	#
+1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	#	Using filesort
 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
 ST_AsText(p1)	ST_AsText(p2)
 POINT(2 4)	POINT(-2 -6)
@@ -1488,7 +1488,7 @@ a	ST_AsText(p)	ST_AsText(l)	ST_AsText(g)
 2	POINT(20 20)	LINESTRING(2 3,7 8,9 10,15 16)	POLYGON((10 30,30 40,40 50,40 30,30 20,10 30))
 EXPLAIN UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	27	NULL	1	#
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	27	NULL	#	Using where
 UPDATE t1 SET p = ST_GeomFromText('POINT(30 30)') WHERE p = ST_GeomFromText('POINT(20 20)');
 SELECT a, ST_AsText(p), ST_AsText(l), ST_AsText(g) FROM t1;
 a	ST_AsText(p)	ST_AsText(l)	ST_AsText(g)
@@ -1526,7 +1526,7 @@ ALTER TABLE t1 DROP PRIMARY KEY;
 ALTER TABLE t1 ADD PRIMARY KEY(p);
 EXPLAIN SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	const	PRIMARY,idx2	PRIMARY	27	const	1	#
+1	SIMPLE	t1	const	PRIMARY,idx2	PRIMARY	27	const	#	
 SELECT a, ST_AsText(p) FROM t1 WHERE a = 2 AND p = ST_GeomFromText('POINT(30 30)');
 a	ST_AsText(p)
 2	POINT(30 30)
diff --git a/mysql-test/suite/innodb_gis/r/point_big.result b/mysql-test/suite/innodb_gis/r/point_big.result
index c471ab6adb2..7248c55bafe 100644
--- a/mysql-test/suite/innodb_gis/r/point_big.result
+++ b/mysql-test/suite/innodb_gis/r/point_big.result
@@ -260,7 +260,7 @@ INSERT INTO gis_point VALUES
 'The ORDER BY for spatial index will use filesort'
 EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	13	#
+1	SIMPLE	gis_point	ALL	NULL	NULL	NULL	NULL	#	Using filesort
 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
 ST_AsText(p1)	ST_AsText(p2)
 POINT(2 4)	POINT(-2 -6)
diff --git a/mysql-test/suite/innodb_gis/r/rt_precise.result b/mysql-test/suite/innodb_gis/r/rt_precise.result
index c83b88b568c..4929dfc99a8 100644
--- a/mysql-test/suite/innodb_gis/r/rt_precise.result
+++ b/mysql-test/suite/innodb_gis/r/rt_precise.result
@@ -22,7 +22,7 @@ test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 EXPLAIN SELECT fid, ST_AsText(g) FROM t1 WHERE ST_Within(g, ST_GeomFromText('Polygon((140 140,160 140,160 160,140 140))'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	gis_key	NULL	NULL	NULL	150	#
+1	SIMPLE	t1	ALL	gis_key	NULL	NULL	NULL	#	Using where
 SELECT fid, ST_AsText(g) FROM t1 WHERE ST_Within(g, ST_GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))')) ORDER BY fid;
 fid	ST_AsText(g)
 1	LINESTRING(150 150,150 150)
diff --git a/mysql-test/suite/innodb_gis/r/types.result b/mysql-test/suite/innodb_gis/r/types.result
index 9eb40dc4231..a337ee10e3d 100644
--- a/mysql-test/suite/innodb_gis/r/types.result
+++ b/mysql-test/suite/innodb_gis/r/types.result
@@ -33,7 +33,7 @@ GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
 CREATE INDEX i_p ON t_wl6455 (g(10));
 EXPLAIN SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t_wl6455	ref	i_p	i_p	12	const	2	#
+1	SIMPLE	t_wl6455	ref	i_p	i_p	12	const	#	Using where
 SELECT ST_AsText(g) FROM t_wl6455 WHERE g = POINT(2,2);
 ST_AsText(g)
 POINT(2 2)