List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 3 1999 9:43pm
Subject:Re: Trying to optimize a join
View as plain text  
>>>>> "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
Thread
Trying to optimize a joinKing Xaymaca3 Apr
  • Re: Trying to optimize a joinKing Xaymaca3 Apr
    • Re: Trying to optimize a joinMichael Widenius4 Apr