Do the 3 tables have different column structures? Or do they all have the
same table structure? For example, is Table1 storing only data for year
1990 and table 2 storing data for 1991 etc? If so you could use a merge
table. (Or do you need transactions, in which case you will need to use
InnoDb and merge tables are not possible)
Is there always a 1:1 relationship between the tables? If so, I'd recommend
storing all the data in 1 table. Joins are really expensive and are to be
avoided if at all possible possible.
One way I found to make joins faster when retrieving under 5k rows, is to
select the rows from each table and put them into a temporary Memory table,
build the primary index on each memory table, and then use the memory
tables for the join. This seems to be more work, but it is about twice as
fast as joining the physical tables together.
Hope this helps. If you could provide us with more information as how you
will be adding and retrieving the data (how many rows are being retrieved
in a query?), perhaps we can narrow down the solution for you.
Mike
At 07:44 AM 5/9/2009, Kyong Kim wrote:
I was wondering about a scale out problem.
Lets say you have a large table with 3 cols and 500+ million rows.
Would there be much benefit in splitting the columns into different tables
based on INT type primary keys across the tables? The split tables will be
hosted on a same physical instance but can be spread over multiple disks.
We're also open to splitting the query and reconstituting the data at the
application layer such as
select col1, col2 from t1 where col2='name';
select col2 from t2 where col1=t1.col1;
select col2 from t3 where col1=t1.col1;
as opposed to
select t1.col2, t2.col2, t3.col2 from t1 inner join t2 on t1.col1=t2.col1
inner join t3 on t1.col1=t3.col1;
My concern to this approach is the overhead of joins of such large number
of rows. I was doing some research into the cost of joins and as of 5.0,
the joins were still nested loop scans.
I was wondering if there are others with practical experience in this
matter and what they've found. Any feedback will be much appreciated.
Kyong
Inst. Web Programmer
CMDBA 5.0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
