Query:
SELECT `MobInfo`.`Name` , `MobInfo`.`Level` AS `MLevel` , `MobInfo`.`ID` , `WeaponTitleData`.`LLevel` , `WeaponTitleData`.`Suffix`
FROM `MobInfo`
JOIN (
SELECT `MobID` , `Level` AS `LLevel` , `Suffix`
FROM `WeaponTitleData`
ORDER BY `LLevel` DESC
) AS `WeaponTitleData` ON `MobInfo`.`ID` = `WeaponTitleData`.`MobID`
WHERE `LLevel` >6
GROUP BY `MobID`
ORDER BY `MLevel`
Output of Explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1111 Using where; Using temporary; Using filesort
1 PRIMARY MobInfo eq_ref PRIMARY PRIMARY 4 WeaponTitleData.MobID 1
2 DERIVED WeaponTitleData ALL NULL NULL NULL NULL 1111 Using filesort
I have Indexes on the columps ID, MobID, and both Level columns. I don't know what else to add