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
