[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
On Sep 23, 2015, at 2:32 AM, <Rhys.Campbell@swisscom.com> <Rhys.Campbell@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.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 _______________________________________________ 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
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 <greenlion@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.Campbell@swisscom.com> <Rhys.Campbell@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.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 _______________________________________________ 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
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:
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 <greenlion@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.Campbell@swisscom.com> < Rhys.Campbell@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.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
_______________________________________________ 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
participants (3)
-
Justin Swanhart -
Rhys.Campbell＠swisscom.com -
Roberto Spadim