mysql slow performance on a small table with join query

I have a couple of tables that i join together when i execute the following query:

SELECT article.year, authors.last_name, count(DISTINCT article.id) as count FROM
article LEFT JOIN authors ON article.id = authors.id WHERE authors.last_name =
'bloggs' GROUP BY article.year

For some reason, this is taking between 6 and 7 seconds to return the results which seems unbelievably slow to me given the relatively small number of rows it has to deal with. Am I doing something wrong here?

If I run an EXPLAIN on the query I get the following:

select_type    table    type   possible_keys  key    key_len    ref    rows    extra
=====================================================================================
simple         article  all    null           null    null      null   762     using temporary; using filesort
simple         authors  all    null           null    null      null   5061    using where; using join buffer

Both tables are InnoDB. I'm running this from my local machine which is fairly low spec (windows xp, 1 ghz, 1gb ram) but even so, I would have thought this would be quicker. If I load a few more rows into the tables it starts to take minutes rather开发者_如何学Go than seconds.

Any thoughts?

Table structures below:

Article:

field    type       null    key    default    extra
=======================================================
id       int        yes            null
year     char(20)   yes            null
volume   char(20)   yes            null
issue    char(20)   yes            null
title    text       yes            null

Authors:

field      type       null    key    default    extra
=======================================================
id         int        yes            null
last_name  char(100)  yes            null
initials   char(10)   yes            null

---

Try adding indexes on columns authors.last_name and authors.id.

But, are you sure your query is ok? shouldn't it look like :

SELECT article.year, authors.last_name, count(DISTINCT article.id) as count FROM
article LEFT JOIN authors ON article.author_id = authors.id WHERE authors.last_name =
'bloggs' GROUP BY article.year

If so, an index on articles.author_id would be required - although, not for this query, but as a general best practice

---

As Tudor said, add indexes. You can also extract the group by.

SELECT * FROM (SELECT article.year, authors.last_name, count(DISTINCT article.id) as count FROM
article LEFT JOIN authors ON article.author_id = authors.id WHERE authors.last_name =
'bloggs') GROUP BY article.year

Doing this you are first fetching by the join, and in the set, applying the aggregate function.

And explain to see where is the spot to the improvement.

Font of the suggestion:

http://kccoder.com/mysql/join-group-by-performance/