List:General Discussion« Previous MessageNext Message »
From:Olexandr Melnyk Date:November 21 2008 8:16pm
Subject:Re: SELECT through many databases
View as plain text  
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@strippedg
>>>
>>>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1artz@the-
>>> infoshop.com
>>>
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=andrematos@stripped
>>
>>
> --
> 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
>
>
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