List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 11 2000 9:41pm
Subject:Re: are indexes used on a self join?
View as plain text  
In the last episode (Apr 11), Mark Maggelet said:
> right you are, it's been a long night.
> here is the whole thing:
> explain select distinct(products.id) from products,kk as a,kk as b where
> (products.id=a.id and a.keyword ='black') and (products.id=b.id and b.keyword
> ='history');
>
>
> +----------+--------+---------------+---------+---------+-------+-------+-------------+
> | table    | type   | possible_keys | key     | key_len | ref   | rows  | Extra      
> |
>
> +----------+--------+---------------+---------+---------+-------+-------+-------------+
> | a        | ref    | keyword       | keyword |      20 | black |  1346 |            
> |
> | products | eq_ref | PRIMARY       | PRIMARY |       4 | a.id  |     1 | Using index
> |
> | b        | range  | keyword       | keyword |    NULL | NULL  | 15751 |            
> |
>
> +----------+--------+---------------+---------+---------+-------+-------+-------------+

It looks like you need an index on kk.id.

-- 
	Dan Nelson
	dnelson@stripped
Thread
are indexes used on a self join?Mark Maggelet11 Apr
  • Re: are indexes used on a self join?Dan Nelson11 Apr
    • Re: are indexes used on a self join?Mark Maggelet11 Apr
  • Re: are indexes used on a self join?Gerald Clark12 Apr
    • Re: are indexes used on a self join?Mark Maggelet12 Apr
      • Re: are indexes used on a self join?Dan Nelson12 Apr