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 > On Sep 23, 2015, at 2:32 AM, <[email protected]> > <[email protected]> wrote: > > 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....@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'm with a new problem > > 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: > > CREATE VIEW view_name AS > SELECT * FROM table1 WHERE primary_key=<a value only at table1> > UNION ALL > SELECT * FROM table2 WHERE primary_key=<a value only at table2> > UNION ALL > SELECT * FROM table3 WHERE primary_key=<a value only at table3> > UNION ALL > SELECT * FROM table4 WHERE primary_key=<a value only at table4> > UNION ALL > ... > 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 > > any idea is well come > > -- > Roberto Spadim > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
