data_source_sql query is unnecessarily slow on MySQL with many tables and derived_merge=off

[blowfishpro]

Steps to reproduce

• Have a rails application using MySQL
• Have a reasonably large number of tables in your database (we have 866)
• Have the optimizer switch derived_merge=off due to many issues in MySQL 5.7
• Use any method that runs data_source_sql (I think rails runs some of these queries automatically when reading the schema, we were also running data_source_exists?(table_name) explicitly

# done explicitly here to make the benchmark results demonstrate the issue
ActiveRecord::Base.connection.disable_query_cache!

ActiveRecord::Base.connection.data_sources.count
# => 866

ActiveRecord::Base.connection.select_value('select @@session.optimizer_switch')[/derived_merge=\w+/]
# => derived_merge=off

Benchmark.measure { 100.times { ActiveRecord::Base.connection.data_source_exists?('schema_migrations') } }
# =>  0.024123   0.002947   0.027070 ( 24.075342)

Benchmark.measure { 100.times { ActiveRecord::Base.connection.select_value('SELECT table_name FROM information_schema.tables WHERE table_schema = database() AND table_name = "schema_migrations"').present? } }
# => 0.010774   0.002236   0.013010 (  0.026767)

Expected behavior

These calls are fast

Actual behavior

These calls are ~900x slower than they could be

System configuration

Rails version: 6.0.4.8

Ruby version: 2.7.5

Additional information

• The behavior was changed from something like the fast query above to the current query in Use a subquery when filtering information_schema.tables by table_name. #39712 to work around another MySQL bug. However we do not use user permissions below the database level so we don't necessarily need that.
• Some of the derived_merge issues are fixed in MySQL 8, however it's not clear that they all are.
• Of course we aren't running this 100 times in quick succession with no query cache, however the individual calls add up over time and can make performance much worse when the database server is already under heavy load.
• Since the table permissions case has to be supported too maybe this could be switchable somehow?