Need help diagnosing a slow query

[Rusty K]

I have a pretty simple database, and I run the same query with different parameters. Most of the time, the query runs in under 1 second. However, about 10% of the time, the query takes 30 to 80 seconds to complete.

My guess is I've not set up either my schema, query, or mysql settings correctly. So I'm including those below.

Does anybody have any feedback on what I can change to make this query fast every time I run it? Or what I can look for to figure that out?

mysql Version: 5.1.50-log
OS: Centos 5.5

Schema:

(There are about 50 million records in the names tables, and about 60 million records in the purchases table)

CREATE TABLE names (
name_id varchar(16) NOT NULL,
firstname varchar(28) DEFAULT NULL,
lastname varchar(28) DEFAULT NULL,
PRIMARY KEY (name_id),
KEY firstname (firstname),
KEY lastname (lastname)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE purchases (
id_purchase varchar(16) NOT NULL,
name_id varchar(16) NOT NULL,
purchase_info varchar(100) NOT NULL,
PRIMARY KEY (id_purchase),
KEY name_id (name_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Query (the firstname and lastname values will change each time):

SELECT names.*, purchases.purchase_info
FROM names
LEFT JOIN purchases
ON names.name_id = purchases.name_id
WHERE
lastname like 'BRO%'
AND firstname like 'JOH%'
LIMIT 501;

Not that I usually only have the first 3 to 8 letters of the first name and last name available for the query.

I'm running this on a 4-processor server with 1GB of RAM. The server is dedicated to mysql so it's the only thing running.

Here's my my.cnf file settings for mysql:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 512M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1