What is the best strategy for partitioning two tables?

Question

I want to partition my 2 tables which are table1(a1,b1) and table2(a2,b2), where a1 and a2 are primary keys of the two tables. So what should be the best strategy for partitioning these two table to support the query:

SELECT * FROM table1, table2 WHERE table1.a1 = table2.a2

ADDITIONAL INFO

And based on that partitioning strategy, I would also want to decide which join can be used to efficiently run the following query:

SELECT * FROM table1, table2 WHERE table1.a1 = table2.b2

Answer 1

In MySQL PARTITION is a special technique for horizontally splitting a table based on some key. This is not what you have described.

"Vertical partitioning" is a loosely applied term that is closer to what you seem to be doing. It refers to having the columns of what 'should' be a single table are split across two tables and the tables are linked in a 1:1 relationship. You have such a relationship via their PRIMARY KEYs.

Your query is an example of doing the JOIN necessary to get the columns back together. (@Maxim's reformulation of the query is better; you should use it.)

There is rarely a valid need to vertically partition a table. Please explain why you want to do it. We can discuss whether your case warrants it. Start by providing SHOW CREATE TABLE for both tables.

Answer 2

I would use Inner Join:

SELECT * FROM table1 INNER JOIN table2 ON table1.a1 = table2.a2;

More Info: http://www.w3schools.com/sql/sql_join.asp