MySql Union Is Slow

[Frank Tirone]

I have 10 MySQL databases. One for each region. I'm using ADF to pull and combine table, same format, into one table for ELT into a Snowflake Data Warehouse. Problem is when a new region comes online, 150 pipelines need to be updated to add the new region's tables.

I attempted to create a view combining the 10+ tables. Performance went down and each ELT is now Taking 3 to 4 times as long translating into many hours longer

Benefit is going forward we just add another union to the view and there are no code changes

Suggestions?

We are trying LIMIT as some other forums suggested.

https://dba.stackexchange.com/questions/181828/speed-slow-down-on-a-union-all-view.

https://stackoverflow.com/questions/10213410/performance-of-mysql-union-limits