>>>>> "King" == King Xaymaca <xaymaca@stripped> writes:
King> This is starting to look better in the explain table but the query runs slower.
King> original query= 20 rows in set (0.08 sec)
King> modified query= 20 rows in set (0.23 sec)
King> 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 ;
King> +-------+--------+---------------+---------+---------+--------+------+-------+
King> | table | type | possible_keys | key | key_len | ref | rows | Extra |
King> +-------+--------+---------------+---------+---------+--------+------+-------+
King> | p | range | PRIMARY | PRIMARY | NULL | NULL | 4062 | |
King> | h | eq_ref | PRIMARY | PRIMARY | 3 | p.huid | 1 | |
King> +-------+--------+---------------+---------+---------+--------+------+-------+
King> 2 rows in set (0.00 sec)
Hi!
The above search method should be faster if there are fewer rows that match
the LIKE. The speed is in the above case largely based on how much of
your table is cached in memory.
Because you are using 'ORDER BY' + limit, it's very hard to make it
much faster, as MySQL must in the above case do 4062*2 random disk reads (to
find the 20 rows (a lot of the disk reads are of course cached by the
OS, but this still takes some time)
Regards,
Monty
