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
