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" ?
