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
> >
> >
