List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:November 21 2008 10:18pm
Subject:RE: SELECT through many databases
View as plain text  
as UNIONS may cause FTS you might want to look at using
JOIN USING(ColumnWhichIsCommonForEveryTable)

(then place unique Indexes on each of the Columns for all Tables)

Martin 
______________________________________________ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business of Sender.
This transmission is of a confidential nature and Sender does not endorse distribution to
any party other than intended recipient. Sender does not necessarily endorse content
contained within this transmission. 




> Date: Fri, 21 Nov 2008 14:21:39 -0500
> From: brenttech@stripped
> To: andrematos@stripped
> Subject: Re: SELECT through many databases
> CC: mysql@stripped
> 
> A MERGE table is just a virtual table that is made up of other tables.
> You treat it no differently than any other table, pretend it's a real
> table.
> 
> You could even create multiple MERGE tables from different tables. A
> good example is Q1, Q2, Q3, Q4, LatestQ, for quarterly information.
> You just roll the underlying tables from one to the other as quarters
> close, you never have to change your scripts or move data around.
> Underlying tables can be members of more than one MERGE table.
> 
> You can even INSERT into them, just specify which underlying table the
> new data is supposed to go into.
> 
> On Fri, Nov 21, 2008 at 2:12 PM, Andre Matos <andrematos@stripped>
> wrote:
> > Sounds interesting, but does the MERGER support complex SELECT statements
> > and LEFT JOIN?
> >
> > Andre
> >
> >
> > On 21-Nov-08, at 1:45 PM, Brent Baisley wrote:
> >
> >> On Fri, Nov 21, 2008 at 12:44 PM, Andre Matos
> <andrematos@stripped>
> >> wrote:
> >>>
> >>> Hello,
> >>>
> >>> Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They all
> >>> have
> >>> the same structure but different data.
> >>>
> >>> I would like perform this select
> >>>
> >>> SELECT TaskDoneOn, TaskDoneBy
> >>> FROM {database}
> >>> WHERE TaskDoneOn IS NOT NULL
> >>>
> >>> and collect the data from all 5 database. However, I would like to
> avoid
> >>> doing something like this:
> >>>
> >>> SELECT TaskDoneOn, TaskDoneBy
> >>> FROM db1
> >>> WHERE TaskDoneOn IS NOT NULL
> >>> UNION
> >>> SELECT TaskDoneOn, TaskDoneBy
> >>> FROM db2
> >>> WHERE TaskDoneOn IS NOT NULL
> >>> UNION
> >>> SELECT TaskDoneOn, TaskDoneBy
> >>> FROM db3
> >>> WHERE TaskDoneOn IS NOT NULL
> >>> UNION
> >>> SELECT TaskDoneOn, TaskDoneBy
> >>> FROM db4
> >>> WHERE TaskDoneOn IS NOT NULL
> >>> UNION
> >>> SELECT TaskDoneOn, TaskDoneBy
> >>> FROM db5
> >>> WHERE TaskDoneOn IS NOT NULL
> >>>
> >>>
> >>> Today I have 5, but tomorrow I can have 50 and I don't want to forget
> any
> >>> database.
> >>>
> >>> Thanks for any help.
> >>>
> >>> Andre
> >>
> >> Create a MERGE table that is all those tables combined. Then you just
> >> need to do 1 select as if it was one table. Just be sure to update the
> >> MERGE table description when ever you add a table.
> >>
> >> Brent Baisley
> >
> > --
> > Dr. André Matos
> > andrematos@stripped
> >
> >
> >
> >
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

_________________________________________________________________
Color coding for safety: Windows Live Hotmail alerts you to suspicious email.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_safety_112008 
Thread
SELECT through many databasesAndre Matos21 Nov
  • Re: SELECT through many databasesPeter Brawley21 Nov
    • Re: SELECT through many databasesAndre Matos21 Nov
      • RE: SELECT through many databasesJerry Schwartz21 Nov
        • Re: SELECT through many databasesAndre Matos21 Nov
          • Re: SELECT through many databasesOlexandr Melnyk21 Nov
            • Re: SELECT through many databasesAndre Matos21 Nov
  • Re: SELECT through many databasesBrent Baisley21 Nov
    • Re: SELECT through many databasesAndre Matos21 Nov
      • Re: SELECT through many databasesBrent Baisley21 Nov
        • RE: SELECT through many databasesMartin Gainty21 Nov
  • Re: SELECT through many databasesClaudio Nanni21 Nov
Re: SELECT through many databasesOlexandr Melnyk21 Nov