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/mysql?
[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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
