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