maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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@xxxxxxxxx> 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@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
>
>