InnoDB: ALTER TABLE ... DROP INDEX scans all of SYS_INDEXES under dict_sys latch (cost grows with total number of indexes on the server)

Summary

The commit phase of an in-place ALTER TABLE ... DROP INDEX (no rebuild) deletes the index from the InnoDB data dictionary with

DELETE FROM SYS_INDEXES WHERE ID=:indexid;

(storage/innobase/handler/handler0alter.cc, function commit_try_norebuild(), line 10729 in mariadb-10.6.20).

The statement is unchanged in the current branches (checked 2026-10-14):

The clustered index of SYS_INDEXES is (TABLE_ID, ID), so a predicate on ID alone cannot use it, and the statement reads every row of SYS_INDEXES, i.e. every index of every InnoDB table on the server.
This happens while the dictionary is locked exclusively, so every other connection that needs to open a table that is not in the table cache waits ("Opening tables") for the duration of the scan.

On servers with many schemas (multi-tenant setups with thousands of databases) a single DROP INDEX on an empty table therefore costs well over 100 ms of exclusive dictionary time, and rolling a DROP INDEX out over all schemas stalls the whole server.

The same file already does the right thing for RENAME INDEX:

UPDATE SYS_INDEXES SET NAME = :new_name WHERE ID = :index_id AND TABLE_ID = :table_id;

Measurements

Server with N empty InnoDB tables (3 indexes each). Measured: the state "Committing alter table to storage engine" (SHOW PROFILE) for one ALTER on a small table, with a buffer pool large enough to hold the dictionary.

The cost is linear, about 34 microseconds per 1000 indexes on the server.
10.11.16 behaves the same.

Under concurrent load (60 client connections running queries that open tables not in the table cache, one session executing DROP INDEX on one table per schema in a loop), table opens per second dropped to less than half, most connections piled up in "Opening tables" for several seconds, and CPU went to saturation.
With DROP INDEX ..., FORCE (table rebuild, old table dropped by TABLE_ID) there was no measurable impact.

How to repeat

1. Create many tables. The number of indexes on the server is what matters, not the schema they are in. Setting up is fsync-bound; innodb_flush_log_at_trx_commit=0 or eatmydata helps.

CREATE DATABASE filler;

DELIMITER ;;

CREATE PROCEDURE filler.mk(IN n INT)

BEGIN

  DECLARE i INT DEFAULT 1;

  WHILE i <= n DO

    SET @s = CONCAT('CREATE TABLE filler.t_', i,

      ' (id INT PRIMARY KEY, a INT, b INT, KEY ka (a), KEY kb (b)) ENGINE=InnoDB');

    PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

    SET i = i + 1;

  END WHILE;

END;;

DELIMITER ;

CALL filler.mk(500000);

-- 500000 tables x 3 indexes = 1.5M rows in SYS_INDEXES

2. Compare DROP INDEX with RENAME INDEX on small tables:

CREATE DATABASE test_drop;

USE test_drop;

CREATE TABLE d (id INT PRIMARY KEY, a INT, b INT, KEY ka (a), KEY kb (a, b)) ENGINE=InnoDB;

CREATE TABLE r LIKE d;

SET profiling = 1;

ALTER TABLE r RENAME INDEX kb TO kb2;

ALTER TABLE d DROP INDEX ka;

SHOW PROFILES;

-- RENAME: "Committing alter table to storage engine" stays around 2 ms

SHOW PROFILE FOR QUERY 1;

-- DROP: "Committing alter table to storage engine" grows with the number of indexes from step 1

SHOW PROFILE FOR QUERY 2;

Stack samples (gdb) during a DROP INDEX loop show the time in
commit_try_norebuild -> que_eval_sql -> row_sel -> sel_set_rec_lock on SYS_INDEXES.

Suggested fix

Include TABLE_ID so the clustered index can be used. The DELETE on SYS_FIELDS is fine, its clustered index is (INDEX_ID, POS).

Patch against mariadb-10.6.20:

--- a/storage/innobase/handler/handler0alter.cc

+++ b/storage/innobase/handler/handler0alter.cc

@@ -10726,11 +10726,12 @@ commit_try_norebuild(

 			"PROCEDURE DROP_INDEX_PROC () IS\n"

 			"BEGIN\n"

 			"DELETE FROM SYS_FIELDS WHERE INDEX_ID=:indexid;\n"

-			"DELETE FROM SYS_INDEXES WHERE ID=:indexid;\n"

+			"DELETE FROM SYS_INDEXES WHERE TABLE_ID=:tableid AND ID=:indexid;\n"

 			"END;\n";

 		pars_info_t* info = pars_info_create();

 		pars_info_add_ull_literal(info, "indexid", index->id);

+		pars_info_add_ull_literal(info, "tableid", index->table->id);

 		error = que_eval_sql(info, drop_index, trx);

 		if (error == DB_SUCCESS && index->type & DICT_FTS) {

Verified on a 10.6.20 source build (stock vs. patched, identical build options, 4.8M rows in SYS_INDEXES):

To check that the patched DELETE removes the right row, the server was restarted after the DROP INDEX,
so that InnoDB loads the table definition from SYS_INDEXES/SYS_FIELDS again instead of using the cached one.
After the restart the dictionary is consistent: the dropped index is gone from INNODB_SYS_INDEXES, CHECK TABLE reports OK,
reads through the remaining secondary index return all rows, and the error log is clean.

row_merge_drop_index_dict() in storage/innobase/row/row0merge.cc (line 3650 in mariadb-10.6.20) contains the same statement.
It is used when cleaning up after a failed index creation and would benefit from the same change, but needs the table id passed in.

Workaround

ALTER TABLE ... DROP INDEX ..., FORCE (or ALGORITHM=COPY)
rebuilds the table and avoids the scan. Cheap for small tables, not for large ones.