I have two tables, A and B, which are located in different databases on the same MySQL server. I would like to create a derived table C from tables A and B, as an inner join between the two tables.

Something like this (pseudo-code):

create table C as select A.gene, B.pubmed_id, B.pubmed_text from A inner join B ...

Will there be a performance hit in doing so? Would it be more prudent to load tables A and B into the same database? These are both very big tables with millions of records.

  • Where are you going to create 'C' . Along with 'A' or with 'B'. Or any 3rd database. Off course, A and B in same database will boost performance as database selection will be one shot. – Koustuv Chatterjee Apr 24 '15 at 17:02
  • I'll create C in the same database as table A. Only table B is in a different database. – ktm5124 Apr 24 '15 at 17:27
  • And what is the impediment you are facing to create table B in the same database where table A resides ? – Koustuv Chatterjee Apr 24 '15 at 18:58
  • We wanted table B to be in a different database, because our application doesn't use it (it's a dependency for some derived tables). – ktm5124 Apr 24 '15 at 20:20

There is essentially no difference between table and db.table. Once the tables (for SELECT or for INSERT) is opened, it is just a table, regardless of what database it is in.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.