List:General Discussion« Previous MessageNext Message »
From:Andre Matos Date:November 21 2008 8:31pm
Subject:Re: SELECT through many databases
View as plain text  
The reason for having many databases with the same "structure" but  
with different data is because for regulatory and compliance  
requirements. Each database belongs to a separate company: company  
specific database. The applications (some written in PHP4 and others  
in J2EE) are unique and they can access one database each time.  
Connect to one database and then disconnect to connect to a different  
one.

The issue is when I need to collect some data for statistics purpose.  
Unfortunately, I cannot have an official application to go through all  
database or even use the MERGE table as suggested here in the list. I  
am not allowed to. That's why I was thinking to use only a SELECT  
statement to do the job as I have been doing.

Thanks,

Andre




On 21-Nov-08, at 3:16 PM, Olexandr Melnyk wrote:

> Hello Andre,
>
> I would recommend you to rethink your criteria (if there's any) for
> splitting data into multiple tables.
>
> Because now. the more tables you add, the more of a performance  
> problem it
> may cause.
>
> --
> Sincerely yours,
> Olexandr Melnyk
> http://omelnyk.net/
>
>
> On Fri, Nov 21, 2008 at 9:58 PM, Andre Matos <andrematos@stripped 
> >wrote:
>
>> Thanks everyone for the help.
>>
>> I was trying to use any API (e.g. PHP, Java, etc). I just gave up  
>> because
>> unfortunately the time fly and the user needs this asap. I am doing  
>> via
>> script.
>>
>> Thanks!
>>
>> Andre
>>
>>
>>
>> On 21-Nov-08, at 2:34 PM, Jerry Schwartz wrote:
>>
>> "Many MySQL APIs (such as PHP) allow you to treat the result  
>> returned from
>>> a
>>> SHOW statement as you would a result set from a SELECT; see  
>>> Chapter 22,
>>> APIs
>>> and Libraries, or your API documentation for more information. In
>>> addition,
>>> you can work in SQL with results from queries on tables in the
>>> INFORMATION_SCHEMA database, which you cannot easily do with  
>>> results from
>>> SHOW statements. See Chapter 20, The INFORMATION_SCHEMA Database."
>>>
>>> You should be able to "refresh" your MERGE table using a programming
>>> language. Get a list of the databases, then construct a CREATE  
>>> TABLE or
>>> what
>>> have you.
>>>
>>> -----Original Message-----
>>>> From: Andre Matos [mailto:andrematos@stripped]
>>>> Sent: Friday, November 21, 2008 2:11 PM
>>>> To: peter.brawley@stripped
>>>> Cc: mysql@stripped
>>>> Subject: Re: SELECT through many databases
>>>>
>>>> I was trying to avoid both since the SELECT statement is not fixed.
>>>> Time to time, users want different information.
>>>>
>>>> Thanks,
>>>>
>>>> Andre
>>>>
>>>> On 21-Nov-08, at 12:59 PM, Peter Brawley wrote:
>>>>
>>>> Andre Matos wrote:
>>>>>
>>>>> Today I have 5, but tomorrow I can have 50 and I don't want to
>>>>>>
>>>>> forget any database.
>>>>>
>>>>> Do it in an app language or as a PREPARED statement in an sproc.
>>>>>
>>>>> PB
>>>>>
>>>>> ---
>>>>>
>>>>> Andre Matos 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
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
> ---------------------------------------------------------------------
>>>>>>
>>>>> ---
>>>>
>>>>>
>>>>>>
>>>>>> Internal Virus Database is out of date.
>>>>>> Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus
>>>>>> Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
>>>>>>
>>>>>>
>>>>>>
>>>> --
>>>> 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=jschwartz@the-
>>>> infoshop.com
>>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>> --
>> 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
>>
>>

--
Dr. André Matos
andrematos@stripped



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