You have a sharded table. You could probably use shard-query. If all tables are in same schema, you will need to create a new "fake" schema for each table:
Create schema s1;
Create view s1.the_table as select * from real_schema.table1;
Create schema s2;
Create view s2.the_table as select * from real_schema.table2;
Set up shard query with N shards, each pointing to on of the "fake" schema.
Select from the_table in Shard-Query to access all the tables. When you set up shard-query, use a fake shard-key so that all tables are always searched (they will be searched in parallel).
OR
Use a stored procedure. It can use dynamic SQL to search each table and return a result set from each, since SP can return more than one result set. This is slower (no parallelism) but simpler.
Sent from my iPhone
Best thing to do it to try it but I suspect your hunch is correct.
Really, it would be a good idea to merge all your data into a single table. Perhaps use partitioning
https://dev.mysql.com/doc/refman/5.5/en/partitioning.html which the optimizer should be able to take advantage of.
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net]
On Behalf Of Roberto Spadim
Sent: Wednesday, September 23, 2015 4:06 AM
To: Maria Discuss
Subject: [Maria-discuss] doubt about merge table (10000+) with same structure
Hi again guys :)
i have MANY (10000+) tables with same struct, and i want to execute a select from "some" (100+) tables
i was thinking about creating a view with all tables, something like:
SELECT * FROM table1 WHERE primary_key=<a value only at table1>
SELECT * FROM table2 WHERE primary_key=<a value only at table2>
SELECT * FROM table3 WHERE primary_key=<a value only at table3>
SELECT * FROM table4 WHERE primary_key=<a value only at table4>
SELECT * FROM tablen WHERE primary_key=<a value only at tablen>
but i don't know if mysql optimizer will do a good job when i execute something like:
SELECT * FROM view_name WHERE primary_key=<a value only located at table1>
and just execute the query at table1 instead of alllllllll 10000+ tables
i'm considering restruture database to a spider engine or any other method if i don't have other option