Using mysql6.0.5 version before, now switch to mysql5.7.20, same database, 6.0.5/6.5.4 version computer a query about 1 to 2 seconds after the restart, but using 5.7.20 versions of the same database as the same query about 12 seconds (a second query speed), what reason be excuse me?

CodePudding user response:

Compare the execution plan, and compare the storage engine,

CodePudding user response:

Multi-table query, the query is simple: the select JGH, MC, b.j e, b.y z from table1. A, (the select JGH, je, yz from table2) b where a. gh=b.j gh, restart the computer after the query 5.7.20 slower than 6.0.05 many for the first time, the second query is soon, version?

CodePudding user response:

reference 1st floor ACMAIN_CHM response:

compare the execution plan, and compare the storage engine,

posted the EXPLAIN, posted on the SHOW CREATE TABLE for analysis,

CodePudding user response:

Query: SELECT a.k ph, PZH, lx, dw, xh, XCGZJ, DJRQ, BXRQ, DQRQ, b.y z, b.j z FROM CLMX a, (the SELECT yz, jz, KPH FROM GDZC WHERE SJRQ='2018-01-31' AND ZCZT='normal') b WHERE a.k ph=b.k ph
Mysql6.0.5 EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra
1 ALL PRIMARY a \ N \ N \ N \ N 65
1 PRIMARY & lt; Derived2 & gt; ALL \ N \ N \ N \ N 5988 Using the where; Using the join buffer
2 DERIVED GDZC ref SJRQ SJRQ 4 15943 Using the where
Mysql5.7.20 EXPLAIN: id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE a \ N ALL PRIMARY \ N \ N \ N \ N 100 65
1 SIMPLE GDZC \ N ref SJRQ, KPH KPH. 23 GDZC a.k ph 1.36 Using 10 where

CodePudding user response:

reference ACMAIN_CHM reply: 3/f

Quote: refer to 1st floor ACMAIN_CHM response:

Compare the execution plan, and compare the storage engine,

posted the EXPLAIN, posted on the SHOW CREATE TABLE for analysis,

Post SHOW CREATE TABLE for analysis,
Post SHOW index from for analysis,


The original poster is best the need analysis of the information posted at a time, or a little toothpaste is too much trouble,

CodePudding user response:

Found the general reasons, GDZC table KPH, SJRQ original create two separate index, now changed to a composite index after query speed by 13 seconds to 0.037 seconds faster, changed the original table also to query time of 0.002 seconds after a composite index, the old version or high efficiency,

CodePudding user response:

6.0.5/6.5.4? Or 5.6.0?

CodePudding user response:

Tip 6.0.5/6.5.4 and 5.7.20, thank moderator