the SQL in sys.schema_unused_indexes is over-simplified, and not aggregated across nodes

[YangKeao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> select * from sys.schema_unused_indexes;                                                                                                                                                                                    +---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| test          | t           | idx_1      |
| test          | t           | idx_2      |
| test          | t           | idx_1      |
| test          | t           | idx_2      |
+---------------+-------------+------------+
4 rows in set (0.00 sec)

An index in different nodes will appear multiple times in the sys.schema_unused_indexes. I forgot to finish the aggregation 🤦.

Re-write the SQL should work well:

CREATE OR REPLACE VIEW sys.schema_unused_indexes AS
		SELECT
			table_schema as object_schema,
			table_name as object_name,
			index_name
		FROM information_schema.cluster_tidb_index_usage
		WHERE
			table_schema not in ('sys', 'mysql', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') and
			index_name != 'PRIMARY'
		GROUP BY table_schema, table_name, index_name
		HAVING
			sum(last_access_time) is null;