List:General Discussion« Previous MessageNext Message »
From:Olexandr Melnyk Date:November 21 2008 8:36pm
Subject:Re: SELECT through many databases
View as plain text  
Unless that's disallowed too, you could use a couple of triggers which would
update the stats table(s) on insert/delete/update to the main tables in each
database.

Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/


On Fri, Nov 21, 2008 at 10:31 PM, Andre Matos <andrematos@mineirinho.org>wrote:

> 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@earthlink.net
>>>>> 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=1the-
>>>>> infoshop.com
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:
>>>> http://lists.mysql.com/mysql?unsub=1nho.org
>>>>
>>>>
>>>>  --
>>> 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=omelnyk@stripped
>>>
>>>
>>>
> --
> 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