mysql join is very slow

Question

hi All I have two tables (MYISAM)

create table A (email varchar(50));
create table B( email varchar(50) key 'email' (email));

Table A has 130K records Table B has 20K records

why does this sql statement take very long time (more than two minutes, then i aborted query by Ctrl+C) Statement is:

select count(*) from user A, tmp B where A.email=B.email;

Thanks

Answer 1

I'd guess that the Query optimizer has nothing to go on. Why don't you try defining indexes on the email columns.

Answer 2

In general, joining on strings is more expensive than joining on shorter data types like int.

You could speed up this query by making sure both email columns are indexed.

Answer 3

If table A has an int ID field then table B should store that ID instead of storing the email string again. That would decrease the DB size and along with indexes would provide a much faster query speed than a string would ever give you.