WL#7737: InnoDB: Support descending indexes
Affects: Server-8.0
—
Status: Complete
This is the InnoDB part of WL#1074, adding support for descending indexes. InnoDB will store an ASC/DESC flag in SYS_FIELDS and in dict_field_t. Change buffering will be prohibited on indexes that contain descending columns, or if the primary key contains descending columns. This could be lifted with Per-index change buffer. The InnoDB SQL parser will not make use of descending indexes. Because of this, we will require FTS_DOC_ID_INDEX(FTS_DOC_ID) to be an ascending index.
dict_field_t: Add the flag is_ascending. dict_index_add_col(), dict_mem_index_add_field(): Add the flag is_ascending. dict_load_field(): Load the ASC/DESC flag from SYS_FIELDS.POS cmp_data_data(), cmp_dfield_dfield(): Add the flag is_asc. Those callers that are only interested whether the result is 0 can pass any value for is_asc. cmp_dfield_dfield_like_prefix(): Rename to cmp_dfield_dfield_eq_prefix(), and return a Boolean instead of a less,equal,greater status. dtuple_coll_cmp(): Rename to dtuple_coll_eq(), and return a Boolean instead of a less,equal,greater status. cmp_dtuple_rec(), cmp_dtuple_rec_with_match_low(), cmp_dtuple_rec_with_match(), cmp_dtuple_is_prefix_of_rec(): Add the parameter dict_index_t, so that we will know which index fields are ascending and which ones are descending. cmp_decimal(), cmp_whole_field(), cmp_data(), cmp_data_data(): Add the parameter is_asc. row_ins_duplicate_online(): Add the parameter dict_index_t. row_merge_tuple_cmp(), row_merge_tuple_sort(): Replace the parameters n_uniq, n_field with index. page_find_rec_max_not_deleted(): Rename to page_find_rec_last_not_deleted(). The last record in a descending index would be the minimum value, not the maximum. row_search_get_max_rec(): Make the function public. Support descending indexes as well. dict_index_is_asc(): Check if an index consists entirely of ascending fields. opt_calc_index_goodness(): Ignore indexes that are not entirely ascending, and assert that descending indexes are not being used. This is only used in the InnoDB internal SQL parser. ibuf_should_try(): Deny change buffering unless dict_index_is_asc(). This can be re-allowed when implementing per-index change buffer. fts_get_max_doc_id(): Use row_search_get_max_rec(). innobase_table_flags(): Check that there is only one column in FTS_DOC_ID_INDEX. We already had a similar check in ADD FULLTEXT INDEX, but the check in CREATE TABLE was more sloppy. innobase_table_flags(): Check that DESC is not specified on FTS_DOC_ID during CREATE TABLE. innobase_fts_check_doc_id_index(): Check that the FTS_DOC_ID_INDEX on FTS_DOC_ID is ascending, not descending.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.