Could anyone please tell me why the following mysql query is triggering both temporary and filesort?
SELECT * FROM peripherals as p, peripheral_traits as pt
WHERE p.hardwareID=12 AND pt.peripheralTraitID=p.peripheralTraitID AND pt.accountID=1 AND p.hidden='0'
ORDER BY pt.visDescription ASC
peripheralTraitID is indexed (in both tables), as well as hidden, accountID, and visDescription.
EXPLAIN gives the following feedback:
<table>
<tr>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>p</td>
<td>ref</td>
<td>hardwareID,peripheralTraitID,hidden</td>
<td>hardwareID</td>
<td>5</td>
<td>const</td>
<td>7</td>
<td>Using where; Using temporary; Using
filesort</td>
</tr>
<tr>
<td>pt</td>
<td>eq_ref</td>
<td>PRIMARY,accountID</td>
<td>PRIMARY</td>
<td>4</td>
<td>p.peripheralTraitID</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
