sql - MySQL simple join using temporary table -

sql - MySQL simple join using temporary table -

i've got (what thought) simple query on mysql database, using explain shows query using temporary table. i've tried changing order of select , bring together no avail. i've reduced tables simplest (to see if issue complexity of tables, still have problem). i've tried 2 basic tables, 1 "name" field, , other foreign key reference table:

a: +-------+--------------+------+-----+---------+----------------+ | field | type | null | key | default | | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | no | pri | null | auto_increment | | name | varchar(128) | no | mul | null | | +-------+--------------+------+-----+---------+----------------+ b: +-------+---------+------+-----+---------+----------------+ | field | type | null | key | default | | +-------+---------+------+-----+---------+----------------+ | id | int(11) | no | pri | null | auto_increment | | a_id | int(11) | no | mul | null | | +-------+---------+------+-----+---------+----------------+

and query:

select a.id, a.name bring together b on a.id = b.a_id order a.name;

which thought simple... list of records in a have records in b, ordered name. alas explain says this:

+----+-------------+-------+--------+---------------+---------+---------+--------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+-------+--------+---------------+---------+---------+--------+------+----------------------------------------------+ | 1 | simple | b | index | a_id | a_id | 4 | null | 2 | using index; using temporary; using filesort | | 1 | simple | | eq_ref | primary | primary | 4 | b.a_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------+------+----------------------------------------------+

it looks should using key on b table reason isn't. feeling i'm missing basic (or rdbms knowledge needs brushing somewhat). ideas why it's using temporary table such simple query?

mysql sql query-optimization