MySQL Forums
Forum List  »  Performance

views, unions and different dbs
Posted by: George Kosmidis
Date: February 24, 2010 08:43AM

Hello everyone,
I have a problem which as it seems I cannot find a way to work around:

Let's say I have two dbs dbA and dbB, and a table in each: dbA.tbl and dbB.tbl.
I also have a third DB which that has a view with union all of the two tables:
CREATE VIEW vTbls AS
SELECT * FROM dbA.tbl
UNION ALL
SELECT * FROM dbB.tbl

In order to have a way of seperating each record in the view I add a column in the view that describes each row:

CREATE VIEW vTbls AS
SELECT 'dbA' AS db ,dbA.tbl.* FROM dbA.tbl
UNION ALL
SELECT 'dbB' AS db ,dbB.tbl.* FROM dbB.tbl

As anyone could expect, it's very very slow to perform a query or a join with this view, and you could imagine that a join between two similar views could be a hell.
Is there a way to work around this problem?

(The real case senario now is that dbA and dbB are eshops, and the third db is the admin db which has to have a way of outputing summaries of both eshops.)

Thank you all.

Options: ReplyQuote


Subject
Views
Written By
Posted
views, unions and different dbs
2559
February 24, 2010 08:43AM
1155
February 25, 2010 10:50AM
1176
March 01, 2010 10:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.