newbie join issue
I've got a task that's gonna require me to compare one table to another and remove the rows from the first table that are found in the second table that match email_address. I'm running 4.0.20a-nt-log. The first table has 10 colomns and about 50K records, and the second table has 46 columns and has about 16K records. I've attempted a number of selects that just sat and hung the computer. I know I must be doing something wrong. I figure I'll need to do a left join on it, but I've not had much experience with joins as such and I need a little assistance. Can anyone help me work this out? If you need more info, let me know. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie join issue
I would expect that the speed problems are due to missing indices. Did you do proper indexing? If unsure, post your table structures and query. Stefan Am Tuesday 20 July 2004 17:45 schrieb Edward Ritter: I've got a task that's gonna require me to compare one table to another and remove the rows from the first table that are found in the second table that match email_address. I'm running 4.0.20a-nt-log. The first table has 10 colomns and about 50K records, and the second table has 46 columns and has about 16K records. I've attempted a number of selects that just sat and hung the computer. I know I must be doing something wrong. I figure I'll need to do a left join on it, but I've not had much experience with joins as such and I need a little assistance. Can anyone help me work this out? If you need more info, let me know. Ed -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie join issue
Stefan: I added an index column to each after I imported. Here's a listing of the two tables. la_entire ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id1| int(3) | | | 0 || | id2| varchar(6) | | | || | first_name | varchar(30) | | | || | last_name | varchar(30) | | | || | street_address | varchar(50) | | | || | city | varchar(30) | | | || | state | char(2) | | | || | zip| varchar(9) | | | || | email_address | varchar(50) | | | || | idx| int(7) | | PRI | NULL| auto_increment | ++-+--+-+-++ 10 rows in set (0.00 sec) ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | int(5) | | | 0 || | county | int(5) | | | 0 || | precinct | int(5) | | | 0 || | last_name | varchar(30) | | | || | first_name | varchar(30) | | | || | src_address| varchar(30) | | | || | src_city | varchar(30) | | | || | src_state | varchar(20) | | | || | src_zip| int(5) | | | 0 || | email_address | varchar(30) | | | || | new_city | varchar(30) | | | || | new_state | varchar(20) | | | || | new_zip| int(5) | | | 0 || | new_zip4 | int(4) | | | 0 || | new_address| varchar(30) | | | || | dma_flag | varchar(4) | | | || | deceased | varchar(4) | | | || | phone | int(12) | | | 0 || | time_zone | varchar(4) | | | || | phone_sol | varchar(4) | | | || | cluster| varchar(4) | | | || | age| varchar(4) | | | || | income | varchar(4) | | | || | pres_child | varchar(4) | | | || | own_rent | varchar(4) | | | || | length_of_res | varchar(4) | | | || | buyer | varchar(4) | | | || | responder | varchar(4) | | | || | gender | varchar(4) | | | || | occupation | varchar(4) | | | || | education | varchar(4) | | | || | donor_prospect | varchar(4) | | | || | scr1ast1 | varchar(4) | | | || | scr1bst1 | varchar(4) | | | || | scr2ast1 | varchar(4) | | | || | scr2bst1 | varchar(4) | | | || | decile1| varchar(4) | | | || | decile2| varchar(4) | | | || | decile3| varchar(4) | | | || | decile4| varchar(4) | | | || | scr1ast2 | varchar(4) | | | || | scr1bst2 | varchar(4) | | | || | decile5| varchar(4) | | | || | decile6| varchar(4) | | | || | dob| varchar(12) | | | || | party | varchar(4) | | | || | idx| int(7) | | PRI | NULL| auto_increment | ++-+--+-+-++ 47 rows in set (0.00 sec) My latest attempt at a query is this: select la_entire.* from la_entire left join la_final on
Re: newbie join issue
What is the 'idx' for when you already have an 'id' column? Also, you need an index on the column that you are joining on; having a single indexed column on a table doesn't automatically improve all queries against that table. Put an index on the 'email_address' fields of both tables. You'll need: ALTER TABLE la_entire ADD INDEX idx_email_address (email_address); ALTER TABLE la_final ADD INDEX idx_email_address (email_address); See: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html On Tue, 2004-07-20 at 09:22, Edward Ritter wrote: Stefan: I added an index column to each after I imported. Here's a listing of the two tables. la_entire ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id1| int(3) | | | 0 || | id2| varchar(6) | | | || | first_name | varchar(30) | | | || | last_name | varchar(30) | | | || | street_address | varchar(50) | | | || | city | varchar(30) | | | || | state | char(2) | | | || | zip| varchar(9) | | | || | email_address | varchar(50) | | | || | idx| int(7) | | PRI | NULL| auto_increment | ++-+--+-+-++ 10 rows in set (0.00 sec) ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | int(5) | | | 0 || | county | int(5) | | | 0 || | precinct | int(5) | | | 0 || | last_name | varchar(30) | | | || | first_name | varchar(30) | | | || | src_address| varchar(30) | | | || | src_city | varchar(30) | | | || | src_state | varchar(20) | | | || | src_zip| int(5) | | | 0 || | email_address | varchar(30) | | | || | new_city | varchar(30) | | | || | new_state | varchar(20) | | | || | new_zip| int(5) | | | 0 || | new_zip4 | int(4) | | | 0 || | new_address| varchar(30) | | | || | dma_flag | varchar(4) | | | || | deceased | varchar(4) | | | || | phone | int(12) | | | 0 || | time_zone | varchar(4) | | | || | phone_sol | varchar(4) | | | || | cluster| varchar(4) | | | || | age| varchar(4) | | | || | income | varchar(4) | | | || | pres_child | varchar(4) | | | || | own_rent | varchar(4) | | | || | length_of_res | varchar(4) | | | || | buyer | varchar(4) | | | || | responder | varchar(4) | | | || | gender | varchar(4) | | | || | occupation | varchar(4) | | | || | education | varchar(4) | | | || | donor_prospect | varchar(4) | | | || | scr1ast1 | varchar(4) | | | || | scr1bst1 | varchar(4) | | | || | scr2ast1 | varchar(4) | | | || | scr2bst1 | varchar(4) | | | || | decile1| varchar(4) | | | || | decile2| varchar(4) | | | || | decile3| varchar(4) | | | || | decile4| varchar(4) | | | || | scr1ast2 | varchar(4) | | | ||
Re: newbie join issue
Thanks, I'll take a look at that. The id isn't unique, so that's why I added the idx column. Does my query look okay beyond that? I'll add the additional indexes and try again. Ed Garth Webb said the following on 7/20/2004 1:03 PM: What is the 'idx' for when you already have an 'id' column? Also, you need an index on the column that you are joining on; having a single indexed column on a table doesn't automatically improve all queries against that table. Put an index on the 'email_address' fields of both tables. You'll need: ALTER TABLE la_entire ADD INDEX idx_email_address (email_address); ALTER TABLE la_final ADD INDEX idx_email_address (email_address); See: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html On Tue, 2004-07-20 at 09:22, Edward Ritter wrote: Stefan: I added an index column to each after I imported. Here's a listing of the two tables. la_entire ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id1| int(3) | | | 0 || | id2| varchar(6) | | | || | first_name | varchar(30) | | | || | last_name | varchar(30) | | | || | street_address | varchar(50) | | | || | city | varchar(30) | | | || | state | char(2) | | | || | zip| varchar(9) | | | || | email_address | varchar(50) | | | || | idx| int(7) | | PRI | NULL| auto_increment | ++-+--+-+-++ 10 rows in set (0.00 sec) ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | int(5) | | | 0 || | county | int(5) | | | 0 || | precinct | int(5) | | | 0 || | last_name | varchar(30) | | | || | first_name | varchar(30) | | | || | src_address| varchar(30) | | | || | src_city | varchar(30) | | | || | src_state | varchar(20) | | | || | src_zip| int(5) | | | 0 || | email_address | varchar(30) | | | || | new_city | varchar(30) | | | || | new_state | varchar(20) | | | || | new_zip| int(5) | | | 0 || | new_zip4 | int(4) | | | 0 || | new_address| varchar(30) | | | || | dma_flag | varchar(4) | | | || | deceased | varchar(4) | | | || | phone | int(12) | | | 0 || | time_zone | varchar(4) | | | || | phone_sol | varchar(4) | | | || | cluster| varchar(4) | | | || | age| varchar(4) | | | || | income | varchar(4) | | | || | pres_child | varchar(4) | | | || | own_rent | varchar(4) | | | || | length_of_res | varchar(4) | | | || | buyer | varchar(4) | | | || | responder | varchar(4) | | | || | gender | varchar(4) | | | || | occupation | varchar(4) | | | || | education | varchar(4) | | | || | donor_prospect | varchar(4) | | | || | scr1ast1 | varchar(4) | | | || | scr1bst1 | varchar(4) | | | || | scr2ast1 | varchar(4) | | | || | scr2bst1 | varchar(4) | | | || | decile1| varchar(4) | | | || | decile2| varchar(4) | | | || | decile3| varchar(4) | | | |
Re: newbie join issue
Thanks, adding the indexes worked beautifully. I'll go knock my head on the desk now. Thanks for your time :) Ed Edward Ritter said the following on 7/20/2004 1:08 PM: Thanks, I'll take a look at that. The id isn't unique, so that's why I added the idx column. Does my query look okay beyond that? I'll add the additional indexes and try again. Ed Garth Webb said the following on 7/20/2004 1:03 PM: What is the 'idx' for when you already have an 'id' column? Also, you need an index on the column that you are joining on; having a single indexed column on a table doesn't automatically improve all queries against that table. Put an index on the 'email_address' fields of both tables. You'll need: ALTER TABLE la_entire ADD INDEX idx_email_address (email_address); ALTER TABLE la_final ADD INDEX idx_email_address (email_address); See: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html On Tue, 2004-07-20 at 09:22, Edward Ritter wrote: Stefan: I added an index column to each after I imported. Here's a listing of the two tables. la_entire ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id1| int(3) | | | 0 || | id2| varchar(6) | | | || | first_name | varchar(30) | | | || | last_name | varchar(30) | | | || | street_address | varchar(50) | | | || | city | varchar(30) | | | || | state | char(2) | | | || | zip| varchar(9) | | | || | email_address | varchar(50) | | | || | idx| int(7) | | PRI | NULL| auto_increment | ++-+--+-+-++ 10 rows in set (0.00 sec) ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | int(5) | | | 0 || | county | int(5) | | | 0 || | precinct | int(5) | | | 0 || | last_name | varchar(30) | | | || | first_name | varchar(30) | | | || | src_address| varchar(30) | | | || | src_city | varchar(30) | | | || | src_state | varchar(20) | | | || | src_zip| int(5) | | | 0 || | email_address | varchar(30) | | | || | new_city | varchar(30) | | | || | new_state | varchar(20) | | | || | new_zip| int(5) | | | 0 || | new_zip4 | int(4) | | | 0 || | new_address| varchar(30) | | | || | dma_flag | varchar(4) | | | || | deceased | varchar(4) | | | || | phone | int(12) | | | 0 || | time_zone | varchar(4) | | | || | phone_sol | varchar(4) | | | || | cluster| varchar(4) | | | || | age| varchar(4) | | | || | income | varchar(4) | | | || | pres_child | varchar(4) | | | || | own_rent | varchar(4) | | | || | length_of_res | varchar(4) | | | || | buyer | varchar(4) | | | || | responder | varchar(4) | | | || | gender | varchar(4) | | | || | occupation | varchar(4) | | | || | education | varchar(4) | | | || | donor_prospect | varchar(4) | | | || | scr1ast1 | varchar(4) | | | || | scr1bst1 | varchar(4) | | | || | scr2ast1 | varchar(4) | | | || | scr2bst1 | varchar(4) | | | || | decile1| varchar(4)
