多表查询,查询语句很简单:select jgh,mc,b.je,b.yz from table1 a,(select jgh,je,yz from table2) b where a.jgh=b.jgh,电脑重启后首次查询5.7.20比6.0.05慢很多,第二次查询都很快,版本问题?
贴出EXPLAIN ,贴出SHOW CREATE TABLE以供分析。
查询:SELECT a.kph,pzh,lx,dw,xh,xcgzj,djrq,bxrq,dqrq,b.yz,b.jz FROM clmx a,(SELECT yz,jz,kph FROM gdzc WHERE sjrq = '2018-01-31' AND zczt='正常') b WHERE a.kph=b.kph mysql6.0.5EXPLAIN:id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL \N \N \N \N 65 1 PRIMARY <derived2> ALL \N \N \N \N 5988 Using where; Using join buffer 2 DERIVED gdzc ref sjrq sjrq 4 15943 Using where mysql5.7.20EXPLAIN:id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE a \N ALL PRIMARY \N \N \N 65 100 \N 1 SIMPLE gdzc \N ref sjrq,kph kph 23 gdzc.a.kph 10 1.36 Using where
贴出SHOW CREATE TABLE以供分析。 贴出SHOW index from 以供分析。 楼主最好一次把需要的分析信息贴出,否则一点点儿挤牙膏太麻烦了。
mysql6.0.5EXPLAIN:id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL \N \N \N \N 65
1 PRIMARY <derived2> ALL \N \N \N \N 5988 Using where; Using join buffer
2 DERIVED gdzc ref sjrq sjrq 4 15943 Using where
mysql5.7.20EXPLAIN:id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE a \N ALL PRIMARY \N \N \N 65 100 \N
1 SIMPLE gdzc \N ref sjrq,kph kph 23 gdzc.a.kph 10 1.36 Using where
贴出SHOW index from 以供分析。
楼主最好一次把需要的分析信息贴出,否则一点点儿挤牙膏太麻烦了。