List:General Discussion« Previous MessageNext Message »
From:King Xaymaca Date:April 3 1999 6:39pm
Subject:Re: Trying to optimize a join
View as plain text  
This is starting to look better in the explain table but the query runs slower.
original query= 20 rows in set (0.08 sec)
modified query= 20 rows in set (0.23 sec)


Modified query:

mysql> explain
    -> select h.artist, h.song, h.alabel, p.uri , p.whits, h.ra_url
    -> from hitlist.htemplate h , xaymaca.pagestats p  
    -> where p.huid = h.luid 
    -> and p.uri LIKE 'http://www.hitlist.com%' 
    -> order by p.whits desc
    -> limit 20 ;
+-------+--------+---------------+---------+---------+--------+------+-------+
| table | type   | possible_keys | key     | key_len | ref    | rows | Extra |
+-------+--------+---------------+---------+---------+--------+------+-------+
| p     | range  | PRIMARY       | PRIMARY |    NULL | NULL   | 4062 |       |
| h     | eq_ref | PRIMARY       | PRIMARY |       3 | p.huid |    1 |       |
+-------+--------+---------------+---------+---------+--------+------+-------+
2 rows in set (0.00 sec)





Jim Faucette wrote:
> Try it with:
> and p.uri LIKE 'http://www.hitlist.com%'
> 
> Also in at least some cases aliased tables don't use indexes. I'm not
> sure that applies here, but it wouldn't hurt to try this without
> aliases. Keep in mind, the only fields that require a tablename are
> those names appearing in both tables.
> 
>   jim...

-- 
Vincent Stoessel-xaymaca@stripped
Xaymaca Studios http://www.xaymaca.com
Thread
Trying to optimize a joinKing Xaymaca3 Apr
  • Re: Trying to optimize a joinKing Xaymaca3 Apr
    • Re: Trying to optimize a joinMichael Widenius4 Apr