MySQL 5.7 sys.schema_redundant_indexes view turning

As a DBA, we often use the SYS view, but in the following situations SQL there is no result for more than 70 seconds. 

SELECT TABLE_SCHEMA, TABLE_NAME,                
REDUNDANT_INDEX_NAME, REDUNDANT_INDEX_COLUMNS 
FROM  sys.schema_redundant_indexes 
WHERE  TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys', 'test') 
GROUP BY TABLE_SCHEMA, TABLE_NAME,  REDUNDANT_INDEX_NAME, REDUNDANT_INDEX_COLUMNS;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived3>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived4>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 4. row ***************************
           id: 4
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
*************************** 5. row ***************************
           id: 3
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
5 rows in set, 1 warning (0.00 sec)

Usually, there will be no problems, but as in this case, there will be problems when there are many tables in the database 

select count(*) from information_schema.STATISTICS;
+----------+
| count(*) |
+----------+
|   141719 |
+----------+

 

What should I do at this time? Let's analyze the SQL plan again and we can see that TYPE is ALL.Why are they all 'ALL' ? Looking at the SQL plan again, we find that ROWS are very small, which causes MySQL to mistakenly believe that the amount of data is very small, and does not use the index. Using join buffer (Block Nested Loop)Actually, there is a lot of data

 

After finding the problem, it is easy to solve. because it is a system table, it can’t analyze , so we make the Using join buffer (Block Nested Loop) off in the session like this .

 

set session optimizer_switch='block_nested_loop=off' ;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived3>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: <derived4>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 388
          ref: redundant_keys.table_schema,redundant_keys.table_name
         rows: 2
     filtered: 50.00
        Extra: Using where
*************************** 4. row ***************************
           id: 4
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
*************************** 5. row ***************************
           id: 3
  select_type: DERIVED
        table: statistics
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned all databases; Using filesort
5 rows in set, 1 warning (0.00 sec)

In the SQL plan, you can see it is use the <auto_key0>

It runs 460 rows in set, 5 warnings (14.99 sec)

Thanks.