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;
Hi!
You may want to revisit the query statement above and see if you can do an outer join. That should help you as far as the query statement goes. Next, on to your indexes...
(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 | |
+------------+------+---------------+------+---------+------+------- +-------+
Judging from the output of your EXPLAIN statement, MySQL is forced to do a full table scan (14004 rows in one table and 23677 rows) in another using your current query statement. At the table-level, you should consider using an index, especially in regards to the columns that you list in your WHERE clause since you say that this is a one-to-many relationship. Going back to your query statement, "field1" in each table could stand to be indexed.
If there is anything else that will help you solve this problem, let me know and I will be happy to provide it!
Try the two steps I mentioned and see if that doesn't help you out.
Thanks In Advance!
--kevin
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
