maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxxxxx> <Rhys.Campbell@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx] 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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp