I thought I'd pass this on for those of you that have slow table joins. I'm
sure a few of you have already figured this out, but if not, here it is.
I have some slow table joins, namely a 6 table join using a primary integer
index field which takes 15 seconds to pull in 18k rows from each of the 35
million row tables. Unfortunately I have to do this 3000 times so the time
adds up.
I was able to speed this up by 2x with small or large number of table joins.
Here's how I did it.
1) Created 6 memory tables, one for each of the tables I'm joining. I
extract the 18k rows from each of the disk based tables,
2) Then build an index on the join column of each memory table.
3) Join the memory tables together.
The time for all the steps is 50% of using MyISAM tables on disk.
Example:
create temporary table mem_table1 type=memory select
col1,col2,..coln from table1 where date='2006-01-01';
alter table mem_table1 add index ix_Primary (Rcd_Id); -- Can omit index
from 1st table
... Repeat for remaining 5 tables....
now join the memory tables together. Here I'm using a left join:
select * from mem_table1 T1 left join mem_table2 T2 on
T1.KeyField=T2.KeyField ...
I figure this is faster because random disk access needed to join a disk
based table is considerably slower than when using a RAM based table. The
overhead of creating the Memory table, extracting the data and building the
index isn't that slow because the data is accessed sequentially as opposed
to the slower random disk access needed for the join.
If anyone wants to try it out, let me know if you see an improvement.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
