EXPLAIN
SELECT sp.LevelName,
CASE
WHEN sp.LevelName ='Cultivar' THEN '01'
WHEN sp.LevelName ='Form' THEN '02'
WHEN sp.LevelName ='Variety' THEN '03'
WHEN sp.LevelName ='Subspecies' THEN '04'
WHEN sp.LevelName ='Species' THEN '05'
WHEN sp.LevelName ='Subseries' THEN '06'
WHEN sp.LevelName ='Series' THEN '07'
WHEN sp.LevelName ='Subsection' THEN '08'
WHEN sp.LevelName ='Section' THEN '09'
WHEN sp.LevelName ='Subgenus' THEN '10'
WHEN sp.LevelName ='Genus' THEN '11'
WHEN sp.LevelName ='Subtribe' THEN '12'
WHEN sp.LevelName ='Tribe' THEN '13'
WHEN sp.LevelName ='Supertribe' THEN '14'
WHEN sp.LevelName ='Subfamily' THEN '15'
WHEN sp.LevelName ='Family' THEN '16'
WHEN sp.LevelName ='Superfamily' THEN '17'
WHEN sp.LevelName ='Parvorder' THEN '18'
WHEN sp.LevelName ='Infraorder' THEN '19'
WHEN sp.LevelName ='Suborder' THEN '20'
WHEN sp.LevelName ='Order' THEN '21'
WHEN sp.LevelName ='Superorder' THEN '22'
WHEN sp.LevelName ='Magnorder' THEN '23'
WHEN sp.LevelName ='Parvcohort' THEN '24'
WHEN sp.LevelName ='Infracohort' THEN '25'
WHEN sp.LevelName ='Subcohort' THEN '26'
WHEN sp.LevelName ='Cohort' THEN '27'
WHEN sp.LevelName ='Supercohort' THEN '28'
WHEN sp.LevelName ='Megacohort' THEN '29'
WHEN sp.LevelName ='Infralegion' THEN '30'
WHEN sp.LevelName ='Sublegion' THEN '31'
WHEN sp.LevelName ='Legion' THEN '32'
WHEN sp.LevelName ='Superlegion' THEN '33'
WHEN sp.LevelName ='Megalegion' THEN '34'
WHEN sp.LevelName ='Parvclass' THEN '35'
WHEN sp.LevelName ='Infraclass' THEN '36'
WHEN sp.LevelName ='Subclass' THEN '37'
WHEN sp.LevelName ='Class' THEN '38'
WHEN sp.LevelName ='Superclass' THEN '39'
WHEN sp.LevelName ='Megaclass' THEN '40'
WHEN sp.LevelName ='Microphylum' THEN '41'
WHEN sp.LevelName ='Infraphylum' THEN '42'
WHEN sp.LevelName ='Subphylum' THEN '43'
WHEN sp.LevelName ='Phylum' THEN '44'
WHEN sp.LevelName ='Superphylum' THEN '45'
WHEN sp.LevelName ='Infrakingdom' THEN '46'
WHEN sp.LevelName ='Subkingdom' THEN '47'
WHEN sp.LevelName ='Kingdom' THEN '48'
WHEN sp.LevelName ='Superkingdom' THEN '49'
WHEN sp.LevelName ='Domain' THEN '50'
ELSE '51'
END as LevelCode,
sp.ID,sp.OK_ID,sp.Family,sp.SName,
sp.Genus,sp.FamilyName,sp.GenusName,sp.CFamilyName,sp.CGenusName,
sp.SPName,sp.Name_La,sp.SAuthor,sp.SNamePrefix,sp.Name_Zh,
sp.ReferDate,sp.Nomen,sp.Preferred,p.ID as PhotoID,count(p.ID) as PhotoCount
FROM `plantsp` sp LEFT JOIN photo p ON sp.ID = p.SPID where sp.ID in ('4998','11631','732881','732882','732883')
GROUP BY sp.ID
ORDER BY LevelCode,PhotoCount desc limit 50
EXPLAIN结果如下
哪些字段加索引可以提升查询速度？
Extra里出现了Using filesort，说明排序没有用到索引，需要怎么加索引才能变成Using Index呢。
还有In查询应该如何优化？
其他的地方还有哪里可以优化的吗？
