Slow query

[peteraguard-google]

Hi,

I have the following straightforward query, which is taking up to 8
seconds.

SELECT markers.*, relevance FROM grouped_matches LEFT JOIN markers ON
(grouped_matches.markerid = markers.markerid);

Table 'grouped_matches' has 338 records and table 'markers' has 5
million. 'markerid' is the primary key of markers.

Effectively it is doing 338 primary key indexed look-ups of the markers
table, but given that it is using the correct index this shouldn't take
that long?

The explain outputs:
+----+-------------+-----------------+--------+-----------------+---------+---------+-------------------------------------+------+-------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+-----------------+--------+-----------------+---------+---------+-------------------------------------+------+-------+
| 1 | SIMPLE | grouped_matches | ALL | PRIMARY | NULL
| NULL | NULL | 338 | |
| 1 | SIMPLE | markers | eq_ref | PRIMARY,fastref |
PRIMARY | 4 | grouped_matches.markerid | 1 | |
+----+-------------+-----------------+--------+-----------------+---------+---------+-------------------------------------+------+-------+

Any thoughts would be appreciated.
Peter

[Bill Karwin]

<pe****************@yahoo.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

I have the following straightforward query, which is taking up to 8
seconds.

Table 'grouped_matches' has 338 records and table 'markers' has 5
million. 'markerid' is the primary key of markers.

Your query and the EXPLAIN output look pretty straightforward.

How long has it been since you ran ANALYZE TABLE on your two tables? Read
the user comment from Walther Diechmann on page
http://dev.mysql.com/doc/refman/5.0/en/explain.html.

Regards,
Bill K.