Bug #46945 enable filesort index rebuild for SPATIAL index
Submitted: 26 Aug 2009 20:10 Modified: 7 Sep 2009 7:19
Reporter: P R Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1, 5.0, 5,1, next bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: filesort, gis, INDEX, KEYCACHE, myisam, Repair, spatial
Triage: Triaged: D5 (Feature request)

[26 Aug 2009 20:10] P R
Description:
please enable filesort index repair no spatial indices.
currently, a keycache repair is the only method being allowed on this type of index.

per lines 4642-4655 of mi_check.c,,

static my_bool mi_too_big_key_for_sort(MI_KEYDEF *key, ha_rows rows)
{
  uint key_maxlength=key->maxlength;
  if (key->flag & HA_FULLTEXT)
  {
    uint ft_max_word_len_for_sort=FT_MAX_WORD_LEN_FOR_SORT*
                                  key->seg->charset->mbmaxlen;
    key_maxlength+=ft_max_word_len_for_sort-HA_FT_MAXBYTELEN;
  }
  return (key->flag & HA_SPATIAL) ||
          (key->flag & (HA_BINARY_PACK_KEY | HA_VAR_LENGTH_KEY | HA_FULLTEXT) &&
	  ((ulonglong) rows * key_maxlength >
	   (ulonglong) myisam_max_temp_length));
}

the decision to repair a table/index via filesort or keycache depends on the return value from the above function. Yet, this function has a specific blocking clause which causes it to always return true if a spatial index exists, thus always forcing a keycache rebuild in all tables with spatial indices.

How to repeat:

1) setup myisam-sort-specific server variables high enough to induce a filesort repair (myisam sort buffer_size, myisam max tmpfile size,etc.)

2) create an empty table, and include both a geometry column & also a spatial index on that column.

3) disable keys
4) (bulk) load this table with data (preferably with a large amount of data)
5) re-enable keys

- due to blocking mechanism in function listed above, mysql will forcibly revert to using 'repair with keycache' instead of the more preferable, faster 'repair with filesort' method.

Suggested fix:
allow the use of 'repair with filesort' for spatial indices.

if filesort repairs were purposely disabled for spatial indices due to issues with index corruption&functionality, then such issues should be resolved A.S.A.P and filesort index repairs should then be applied to spatial indices as well. 

I marked this as 'serious' instead of a feature request or performance.
it is not practical to allow the GIS functionality to continue to be handicapped by forcing 'keycache' repairs; it renders the mysql spatial extensions practically useless for tables with large amounts of spatial data that needs to be indexed.
[7 Sep 2009 7:19] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Sep 2009 7:19] Sveta Smirnova
test case

Attachment: bug46945.test (application/octet-stream, text), 2.92 KiB.

[7 Sep 2009 7:20] Sveta Smirnova
option file

Attachment: bug46945-master.opt (application/octet-stream, text), 74 bytes.