Re: Slow query join problem
Yes, you are correct. I was thinking that wouldn't be the case, but it makes sense now. I added indexes and then the query returned in a few seconds. Definitely have to have indexes. Thanks! Dan Nelson wrote: In the last episode (Sep 18), Allen said: I have two tables. One table is 13 million rows the other is about 250,000. I am trying to join the two to find users. The tables are not indexed and I know that will effect the speed, but the join never completes. I let it run for about 10 hours and the process was stuck in the "sending" state, which I don't really understand. What is taking so long? The join size should be no greater than 250,000 which is the size of the second table. Yes??? I am using the default join_buffer size, which seems to low. Might this be the problem? You _have_ to add indexes. Mysql is currently scanning the entire 250k table for each record in the big table to find matching records. You can verify this by running "EXPLAIN SELECT .. etc" on your query and multiplying all the "rows" values together. -- Allen Brost Motorola - DMTS [EMAIL PROTECTED] Work: (847)-435-2019 Cell: (847)-878-7784 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query join problem
Allen wrote: I have two tables. One table is 13 million rows the other is about 250,000. I am trying to join the two to find users. The tables are not indexed and I know that will effect the speed, but the join never completes. I let it run for about 10 hours and the process was stuck in the "sending" state, which I don't really understand. What is taking so long? The join size should be no greater than 250,000 which is the size of the second table. Yes??? I am using the default join_buffer size, which seems to low. Might this be the problem? Well, running a join on tables without indexes is like running a simple select on a table that is the cartesian product of both tables. In your case, you are trying to run a select on a table with: 13million x 250,000 = 3,250,000,000,000 rows ! I am afraid that you won't get any result soon, even with a big join buffer :( Regards, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query join problem
In the last episode (Sep 18), Allen said: > I have two tables. One table is 13 million rows the other is about > 250,000. I am trying to join the two to find users. The tables are not > indexed and I know that will effect the speed, but the join never > completes. I let it run for about 10 hours and the process was stuck in > the "sending" state, which I don't really understand. What is taking so > long? The join size should be no greater than 250,000 which is the size of > the second table. Yes??? > I am using the default join_buffer size, which seems to low. Might this be > the problem? You _have_ to add indexes. Mysql is currently scanning the entire 250k table for each record in the big table to find matching records. You can verify this by running "EXPLAIN SELECT .. etc" on your query and multiplying all the "rows" values together. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query join problem
I have two tables. One table is 13 million rows the other is about 250,000. I am trying to join the two to find users. The tables are not indexed and I know that will effect the speed, but the join never completes. I let it run for about 10 hours and the process was stuck in the "sending" state, which I don't really understand. What is taking so long? The join size should be no greater than 250,000 which is the size of the second table. Yes??? I am using the default join_buffer size, which seems to low. Might this be the problem? -- Allen Brost -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
