shaoyu-li opened a new issue, #12363:
URL: https://github.com/apache/gravitino/issues/12363
### What would you like to be improved?
Two issues in the MySQL DDL under scripts/mysql/:
1. Six index names are reused across 15 tables: idx_mid, idx_cid,
idx_sid, idx_rid, uk_sid_tn_del and uk_sid_fn_del.
MySQL scopes index names per table, so this is valid, but the names
are ambiguous. idx_mid indexes metalake_id on most tables, yet it
indexes metadata_object_id on tag_relation_meta and
policy_relation_meta. Reading an EXPLAIN or a slow query log that
mentions idx_mid therefore does not tell you which column is being
used without also knowing the table.
It also prevents these scripts from being consumed by tooling that
requires index names to be unique across the whole schema.
2. table_version_info has no PRIMARY KEY. It declares only
UNIQUE KEY uk_table_id_version_deleted_at (table_id, version,
deleted_at). For InnoDB this means the table is clustered on an
invisible generated key, and every lookup through the unique key
costs a secondary index traversal plus a lookup into the clustered
index.
### How should we improve?
1. Prefix each duplicated index name with its table name, so that
idx_mid on table_meta becomes table_meta_idx_mid. Renaming an index
is a metadata-only operation from MySQL 5.7 onward, and no code
selects an index by name.
2. Mark table_version_info.version and .deleted_at as NOT NULL and
promote the existing unique key to
PRIMARY KEY (table_id, version, deleted_at). Promoting the existing
key is preferable to adding a surrogate auto-increment id: it keeps
the row narrow and lets lookups hit the clustered index directly.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
