SQL语句如下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查询应该如何优化?
其他的地方还有哪里可以优化的吗?
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查询应该如何优化?
其他的地方还有哪里可以优化的吗?
如果还不行,把IN换成=,IN的5个值,换成UNION ALL关联。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')
WHERE sp.ID = p.SPID where sp.ID in ('4998','11631','732881','732882','732883')
GROUP BY sp.ID
ORDER BY LevelCode,PhotoCount desc limit 50
谢谢大佬,请问ON后边的内容,放到WHERE里。和直接用ON的效率,有什么区别?搞不懂。
你要用就逃不了。。
但可以从另一方式处理。。看你的语句,其实主要是要数量才用的group;要不你给sp表加个photo数量的字段,photo新增和删除的时候,来更新这个字段。。
你这里就都不用连表查询了
2.如果sp.id是主键,增加索引 sp.levelName即可,否则建立组合索引。
3.其它也看出可以优化的。
4.建议用新版mysql,自动优化了不少。你这里重点操作的是临时表,有的建立索引没用。