Hi justin! well, i have two options now... continue with many tables or create one big table my doubt is, show i continue with many tables, or create a big table and use partition? table have same create table struct and the table have differ with the primary key value
for example primary key (i,values) i=1 -> table1 i=1000 -> table1000 if i need a big select like select from tables where i in (1,2,3,4,5,6.....,100) i will need 100 select union all the point is should i create a big table with all tables and use partition (i don't like mysql/mariadb partition) or continue with all tables and use a shard query, or spider engine, or view, or any other tool? i'm considering shard query a long time but i never used it intensivelly to know if i should prefer it or a database engine (i don't have experience, thats the point), any help is wellcome :) i know you are "the guy" at shard query, many thanks for this tool, and congratulations, i read some code of shard query and it's a very beautiful work, i didn't tested yet but i think i will need it or some similar solution Em quarta-feira, 23 de setembro de 2015, Justin Swanhart < [email protected]> escreveu: > 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 <[email protected]> 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, <[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
