List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 11 2000 8:18pm
Subject:Re: are indexes used on a self join?
View as plain text  
Mark Maggelet wrote:

> >In the last episode (Apr 11), Mark Maggelet said:
> >> Hello, can somebody give me some feedback on a query like this:
> >> select distinct(products.id) from products,keywords as a,keywords as b
> >>    where (products.id=a.id and a.keyword ='black') and (b.keyword
> ='history');
> >>
> >> This query is taking 24 seconds, which is way too long. products.id has an
> index,
> >> and keywords.keyword also has an index, but I'm not sure that the index for
> >> keywords.keyword is being used since I alias it twice.
> >what does an EXPLAIN on the query look like?
> >--
> >       Dan Nelson
> >       dnelson@stripped
>
> here's what explain looks like:
>
> mysql> explain select distinct(products.id) from products,kk as a,kk as b where
> (products.id=a.id and a.keyword ='black') 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 |            
> |
>
> +----------+--------+---------------+---------+---------+-------+-------+-------------+
>

Shouldn't  where also include "and products.id=b.id" ?

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