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 <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED] >> >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:[EMAIL PROTECTED] >>>>> Sent: Friday, November 21, 2008 2:11 PM >>>>> To: [EMAIL PROTECTED] >>>>> Cc: mysql@lists.mysql.com >>>>> 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 >>>>> [EMAIL PROTECTED] >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> MySQL General Mailing List >>>>> For list archives: http://lists.mysql.com/mysql >>>>> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >>>>> infoshop.com >>>>> >>>>> >>>> >>>> >>>> >>>> >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: >>>> http://lists.mysql.com/[EMAIL PROTECTED] >>>> >>>> >>>> -- >>> Dr. André Matos >>> [EMAIL PROTECTED] >>> >>> >>> >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >>> >>> >>> > -- > Dr. André Matos > [EMAIL PROTECTED] > > > >
