List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 2 1999 4:26am
Subject:Re: simple left join performance question
View as plain text  
albert braun wrote:
> 
> Hi,
> 
> I need ideas on how to speed up this kind of query. It takes a very very
> long time on two tables with less than 5000 rows each:
> 
> select t1.c1 from t1 left join t2 on t1.c1 = t2.c1 where t2.c1 is null;
> 
> Here are the two simple tables I ran it on.
> 
> mysql> describe t1;
> --------------
> describe t1
> --------------
> 
> +-------+-------------+------+-----+---------+-------+
> | Field | Type        | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | c1    | varchar(32) | YES  |     | NULL    |       |
> +-------+-------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
> 
> mysql> describe t2;
> --------------
> describe t2
> --------------
> 
> +-------+-------------+------+-----+---------+-------+
> | Field | Type        | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | c1    | varchar(32) | YES  |     | NULL    |       |
> +-------+-------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
> 
> These two tables are almost identical. The values their single field
> contains are simply sequential integers.
> t1 has 4999 rows.
> t2 has 4899 rows.
> 
> The return list from the query was
> 4900
> 4901
> 4902
> ....
> 4999
> 
> Thanks
> Albert
> 

- Added indeces on both columns
- change varchar to char, and possibly to int, or even mediumint if you
can get away with it
- make the default NOT NULL if possible

-- 
Sasha Pachev
http://www.sashanet.com
Thread
simple left join performance questionalbert braun2 Jul
  • Re: simple left join performance questionSasha Pachev2 Jul