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