Hello!
I have a couple of tables I am doing a join on. Both have 6 fields. Most fields have fewer than 16 characters in them, but at most 75. Neither table is huge: one with 14004 rows and the other with 23677. I created a temp table to insert the data into. When I perform the join, it takes about 17 minutes to complete. The result is only 23674 rows.
Question is, why is this query taking so long? I have searched the manual and only found an example where they talk about millions of rows being slow and mine has far fewer than that. It is a one to many relationship between the two tables, but should that really slow things down that much? Is there a way to speed things up a little...or a lot?
Just for reference, here is the syntax I am using:
insert into tmp1 select table2.field1, table1.field2, table1.field3, table2.field2, table2.field3, table2.field4, table2.field5, table2.field6, table1.field4, table1.field5, table1.field6 from table2,table1 where table2.field1=table1.field1;
(I have tried the same query with the tables reversed, i.e, "table1.field1=table2.field1" but it still takes a long time.)
MySQL Version: 4.0.15 Mac OS X 10.3.7
Explain:
+------------+------+---------------+------+---------+------+------- +-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------+------+---------------+------+---------+------+------- +-------+
| table1 | ALL | NULL | NULL | NULL | NULL | 14004 | |
| table2 | ALL | NULL | NULL | NULL | NULL | 23677 | |
+------------+------+---------------+------+---------+------+------- +-------+
If there is anything else that will help you solve this problem, let me know and I will be happy to provide it!
Thanks In Advance!
--kevin
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
