The SELECT Anderson d, a.n ame, p.n ame AS platformName, COUNT (1)
The FROM xx_coupon AS a
LEFT the JOIN msds_saleplat AS p=p.o ON Amy polumbo latform rder_plaform
LEFT the JOIN (
The SELECT coupon, COUNT (1) recCount
The FROM xx_coupon_card
GROUP BY coupon
) AS t1 ON Anderson, d=t1. Coupon
LEFT the JOIN (
SELECT the coupon, the SUM (useCount) useCount
The FROM (
The SELECT Arthur c. oupon, COUNT (1) the useCount
The FROM xx_coupon_code c
INNER JOIN xx_order o ON o. chua oupon_code=c.i d
WHERE o.o rder_status!=3 AND o.p ayment_status=2 AND
O. chua oupon_code & gt;=0 AND coupons IS NULL
GROUP BY Arthur c. oupon
UNION ALL
The SELECT Arthur c. oupon, COUNT (1) the useCount
The FROM xx_order o
INNER JOIN xx_order_item_coupon xc ON xc. The orders=o.i d
INNER JOIN xx_coupon_code c ON xc. Coupon_code=c.i d
WHERE o.o rder_status!=3 AND o.p ayment_status=2 AND
O. chua oupon_code IS NULL AND o. chua oupons & gt;=0
GROUP BY Arthur c. oupon
) k GROUP BY coupon
) AS t2 ON Anderson, d=t2. Coupon
WHERE Anderson s_giftcards=0 AND a.t ype!=2 AND a.e nd_date & gt; AND
='2019-12-09'A. egin_date & lt;
='2020-01-09'The ORDER BY DESC Anderson, d

CodePudding user response:

Turn LEFT into a temporary table,

CodePudding user response:

You should A list the amount of data is large, the database is to put A table and other tables all associated, and the results after the WHERE, so will be efficient, if just query, recommends that the result of A table after the WHERE to the temporary table and associated with other tables

CodePudding user response:

1. Check the slow speed is which link
2. Creating a suitable index

CodePudding user response:

Look at the execution plan, see slow in where?

CodePudding user response:

The SELECT coupon, COUNT (1) recCount
The FROM xx_coupon_card
GROUP BY coupon
The SQL for 9 seconds, the data quantity 35 million

CodePudding user response:

Coupon have index, in view of the coupon to build a clustered index

CodePudding user response:

references of the 6th floor of 1 reply:

have coupon index, in view of the coupon to build a clustered index see

have indexes

CodePudding user response:

refer to 7th floor xxs5258 response:

Quote: references conveniently for 6th floor response: 1

Coupon have index, in view of the coupon to build a clustered index see

indexed

Index whether there is a problem, rebuild index have a look, I just tried the amount of data about your list takes 1200 ms

CodePudding user response:

references of the eighth floor of 1 reply:

Quote: refer to 7th floor xxs5258 response:

Quote: reference of the 6th floor of 1 reply:

Coupon have index, in view of the coupon to build a clustered index see

indexed

Index whether there is a problem, rebuild index and see, I've just tried with you about the amount of data table takes 1200 ms

I see the data list in the face of a field set up two index

CodePudding user response:

references 9 f xxs5258 response:

Quote: refer to the eighth floor for those routes 1 response:

Quote: refer to 7th floor xxs5258 response:

Quote: reference of the 6th floor of 1 reply:

Coupon have index, in view of the coupon to build a clustered index see

indexed

Index whether there is a problem, rebuild index and see, I've just tried with you about the amount of data table takes 1200 ms

I see the data list in the face of a field set up two index

Stick the execution plan and have a look

CodePudding user response:

CodePudding user response:

conveniently and reference to the tenth floor of 1 reply:

Quote: references 9 f xxs5258 response:

Quote: references of the eighth floor of 1 reply:

Quote: refer to 7th floor xxs5258 response:

Quote: reference of the 6th floor of 1 reply:

Coupon have index, in view of the coupon to build a clustered index see

indexed

Index whether there is a problem, rebuild index and see, I've just tried with you about the amount of data table takes 1200 ms

I see the data list in the face of a field set up two index

Stick it execution plan see

CodePudding user response:

Is the execution plan

CodePudding user response:

Or plug table data into a new table, for the new table indexed, query the same statement in the new table again and have a look

CodePudding user response:

references of the 14th floor of 1 reply:

or plug table data into a new table, for the new table indexed, query the same statement in the new table again see

I try