Quote: refer to the original poster lxlnulinuli response:

The SELECT stIsid,
The SUM (codeValue) codeValue,
Days
The from (
Select
A.s tIsid,
A. d. vIsid,
A.t agIsid,
(Max (CAST (a.c odeValue as SIGNED)) - MIN (CAST (a.c odeValue as SIGNED))) codeValue,
DATE_FORMAT (a.u pdateTime, "Y - m - % d % %") days
The from tbeqmhistorydata a
LEFT the JOIN tbcodeinfo b on a.t agIsid=b.t agIsid
LEFT the JOIN eqmpowerinfo c on a. d. vIsid=c.d vIsid
WHERE biggest odeType="EQM_YGDN" and c.d vType='DLY'
AND a.u pdateTime & gt; DATE_FORMAT (now (), '% Y - % m - 01 00:00:00) and a.u pdateTime & lt; Now ()
GROUP BY days, b.t agIsid)
Zz GROUP BY days

This is my query, SQL query is September this month data for 12 seconds, and then I found it checked tbeqmhistorydata EXPLAIN about this table 691315 rows of data, updateTime column I added the index and data just also in September 64800, but I select count (*) from a 734389 tbeqmhistorydata records, this is all the records, including 7,8,9 month of data, why do you want to check for such a long time

MySQL JOIN algorithm is still relatively weak, does not support the HASH does not support the merge, it is suggested that try the temporary table split under

MS SQL, isn't it? How is the MySQL?

CodePudding user response:

There are often many MySQL problems for this column

CodePudding user response:

The SELECT stIsid,
The SUM (codeValue) codeValue,
only - here this days is derived from the subquery DATE_FORMAT () function, so the index is invalid
The from (

And you still use days do group, must be slow, the index didn't effect
GROUP BY days, b.t agIsid

CodePudding user response:

The SELECT stIsid,
The SUM (codeValue) codeValue,
Days
The from (
select
A.s tIsid,
A. d. vIsid,
A.t agIsid,
(Max (CAST (a.c odeValue as SIGNED)) - MIN (CAST (a.c odeValue as SIGNED))) codeValue,
DATE_FORMAT (a.u pdateTime, "Y - m - % d % %") days
The from tbeqmhistorydata a
LEFT the JOIN tbcodeinfo b on a.t agIsid=b.t agIsid
LEFT the JOIN eqmpowerinfo c on a. d. vIsid=c.d vIsid
WHERE biggest odeType="EQM_YGDN" and c.d vType='DLY'
AND a.u pdateTime & gt; DATE_FORMAT (now (), '% Y - % m - 01 00:00:00) and a.u pdateTime & lt; Now ()
GROUP BY days, b.t agIsid
)
Zz GROUP BY days

How long will only run red with? To get rid of GROUP BY days, b.t agIsid use how long? CodeType and indexed dvType?

CodePudding user response:

Agree with # 4!

CodePudding user response:

Only run the red part is very slow, in fact I have no function in the query conditions, AND a.u pdateTime & gt; DATE_FORMAT (now (), '% Y - % m - 01 00:00:00) and a.u pdateTime & lt; Now ()

CodePudding user response:

Don't write is greater than the number is less than the symbols, use other methods instead of