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.