If all your tables have the same structure, then all the data really belongs to one table. Let's call it XY
for the sake of giving it a name here.
The table name "A1, B1" ... etc should be just another field in the XY table:
Moz - Attr1 - attr2 - context
- - - A1
- - - B1
This table indeed needs to be properly indexed.
Assuming the Moz
field is a primary key for each table, you then need to have (Moz,Context) as primary key in order to avoid issues with duplicate Moz
values.
Getting an union of all tables is then just as simple and fast as a SELECT * from XY
.
If you need to select a few of the tables: SELECT * from XY WHERE context IN ('A1', 'B1')
Generally speaking, it is a very bad idea to use dynamically created tables in a SQL RDBMS, because it completely defeats the purpose of "Relational", and will cause performance to be abysimal.
You just actually found out the hard way, because technically, your A1, B1, C1 tables are unrelated, and yet you want them to become related via the use of a union, which you don't want to use because it is "slow".
By storing the name as a field, you create a relation between the "table list" table and XY, can now use reasonably fast joins, and no further "CREATE TABLE" are ever needed.
See Database normalization to have a better explanation of these concepts.