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) | | | | | > | 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 > la_entire.email_address = la_final.email_address where > la_final.email_address is null; > > Any help? > > Ed > > Stefan Kuhn said the following on 7/20/2004 12:05 PM: > > 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 > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]