[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
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'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
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
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 < greenlion@gmail.com> escreveu:
participants (3)
-
Justin Swanhart
-
Rhys.Campbell@swisscom.com
-
Roberto Spadim