Hi, I see the PK is the shard-key. In that case shard-query will search only one table, but you will have to populate the mapping table so SQ knows in which table to look.
Sent from my iPhone > On Sep 23, 2015, at 7:55 AM, Justin Swanhart <greenl...@gmail.com> wrote: > > 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, <rhys.campb...@swisscom.com> >> <rhys.campb...@swisscom.com> 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 : maria-discuss@lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp