Bug #77332 ha_innobase::records_in_range() returns constant for spatial indexes
Submitted: 12 Jun 2015 10:33 Modified: 6 Jul 2015 14:46
Reporter: Norvald Ryeng Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[12 Jun 2015 10:33] Norvald Ryeng 
Description:
ha_innobase::records_in_range() hardcodes the number of records in range for spatial indexes:

	/* GIS_FIXME: Currently, we can't support estimate records on
	R-tree index. */
	if (dict_index_is_spatial(index)) {
		n_rows = 2;
		goto func_exit;
	}

This causes the optimizer to (almost?) always use spatial indexes, even if more efficient access methods exist.

How to repeat:
Read the code.

Suggested fix:
Report a real estimate, e.g., (area of query bbox * rows in index)/(area of root node bbox), or something more accurate.
[2 Jul 2015 14:27] Shaohua Wang 
Posted by developer:
 
Refer to rtree_estimate() in MyISAM, return a more accurate estimation
for spatial index in InnoDB.
[6 Jul 2015 14:46] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.8, 5.8.0 release, and here's the changelog entry:

The records_in_range function returned a constant value for spatial
indexes. 

Thank you for the bug report.