Hello,

Does anybody have any info on cross-database joins?

i.e. doing a join across various tables that are located inside
different databases (albeit on the same database server).

I'm interesested in any potential pitfalls, limitations, and performance
issues. There doesn't appear to be much information about this on the
web (at least not that I can find so far).

Does querying multiple databases in a single query count as multiple
connections?

I'm using the InnoDB engine. Do foreign key constraints work across
different databases too?

Is using cross-database joins considered good practice?

Thanks in advance for any .

Ben

On Wed, 2008-04-02 at 21:34 +0100, Ben Roberts wrote:

> Hello,
>
> Does anybody have any info on cross-database joins?
>
> i.e. doing a join across various tables that are located inside
> different databases (albeit on the same database server).
>
> I'm interesested in any potential pitfalls, limitations, and performance
> issues. There doesn't appear to be much information about this on the
> web (at least not that I can find so far).

As far as I can tell, this is exactly the same as querying tables in the
same database.

> Does querying multiple databases in a single query count as multiple
> connections?

No.

> I'm using the InnoDB engine. Do foreign key constraints work across
> different databases too?

I've never tried that. If there is such a relationship, it would tend to
suggest that the tables should be in the *same* database anyway. But you
can try it and see.

> Is using cross-database joins considered good practice?

If separating tables into different databases makes things easier for
you, then ( at least with MySQL ), I don't see any problems.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2
9922 7989
email: dkasak@nusconsulting.com.au
website:
http://www.nusconsulting.com.au

On Thu, Apr 3, 2008 at 2:04 AM, Ben Roberts <list-support@headsnet.com>
wrote:

>
> Hello,
>
> Does anybody have any info on cross-database joins?
>
> i.e. doing a join across various tables that are located inside different
> databases (albeit on the same database server).


Yes, Implemented and working fine without any issue.

>
> I'm interesested in any potential pitfalls, limitations, and performance
> issues. There doesn't appear to be much information about this on the web
> (at least not that I can find so far).
>
> Does querying multiple databases in a single query count as multiple
> connections?

No

I'm using the InnoDB engine. Do foreign key constraints work across
> different databases too?
>
Haven't tried

>
> Is using cross-database joins considered good practice?

Its just like a single database

>
>
> Thanks in advance for any .
>
> Ben
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=p...tikc@gmail.com
>
>


--
Krishna Chandra Prajapati
Email-id: prajapatikc@gmail.com

Hi,

On Wed, Apr 2, 2008 at 4:34 PM, Ben Roberts <list-support@headsnet.com> wrote:
>
> Hello,
>
> Does anybody have any info on cross-database joins?
>
> i.e. doing a join across various tables that are located inside different
> databases (albeit on the same database server).
>
> I'm interesested in any potential pitfalls, limitations, and performance
> issues. There doesn't appear to be much information about this on the web
> (at least not that I can find so far).

There are sometimes queries that are hard to write so MySQL doesn't
complain about an error, but this is not hard to avoid:
http://www.xaprb.com/blog/2006/08/07...ases-in-mysql/

> I'm using the InnoDB engine. Do foreign key constraints work across
> different databases too?

FK constrraints work fine across databases.

Regards
Baron

--

Baron Schwartz, Senior Consultant, Percona Inc.
Tel: +1 888 401 3401 ext 507
24/7 Emergency Line +1 888 401 3401 ext 911
Our Services: http://www.percona.com/services.html
Our Blog: http://www.mysqlperformanceblog.com/

Learnt more from here.

On Mon, Apr 7, 2008 at 9:22 AM, Baron Schwartz <baron@percona.com> wrote:

> Hi,
>
> On Wed, Apr 2, 2008 at 4:34 PM, Ben Roberts <list-support@headsnet.com>
> wrote:
> >
> > Hello,
> >
> > Does anybody have any info on cross-database joins?
> >
> > i.e. doing a join across various tables that are located inside
> different
> > databases (albeit on the same database server).
> >
> > I'm interesested in any potential pitfalls, limitations, and
> performance
> > issues. There doesn't appear to be much information about this on the
> web
> > (at least not that I can find so far).
>
> There are sometimes queries that are hard to write so MySQL doesn't
> complain about an error, but this is not hard to avoid:
>
> http://www.xaprb.com/blog/2006/08/07...ases-in-mysql/
>
> > I'm using the InnoDB engine. Do foreign key constraints work across
> > different databases too?
>
> FK constrraints work fine across databases.
>
> Regards
> Baron
>
> --
>
> Baron Schwartz, Senior Consultant, Percona Inc.
> Tel: +1 888 401 3401 ext 507
> 24/7 Emergency Line +1 888 401 3401 ext 911
> Our Services: http://www.percona.com/services.html
> Our Blog: http://www.mysqlperformanceblog.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=y...0608@gmail.com
>
>


--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn