SQL statements:
SELECT
O.o rder_id,
The SUM (o.d iscountAmount) AS discountAmount,
S.s tore_name,
O. chua reate_time AS create_time,
O. chua ashPay AS cashAmount,
O. chua ardPay AS cardAmount,
M. NAME AS realname,
Grown n,
The SUM (o.o rder_amount) AS order_amount,
O.a liPay AS aliPay,
O.w echatPay AS wechatPay,
O.p ayeasePay AS payeasePay
The FROM
Es_daycount_order o
LEFT the JOIN es_daycount_order_items I ON i.o rder_id=o.o rder_id
LEFT the JOIN es_goods AS g ON i.g oods_id=g.g oods_id
LEFT the JOIN es_member AS m ON o.o perator_id=m.m ember_id
LEFT the JOIN ON Margaret spellings tore_id es_store AS s=s.s tore_id
WHERE
Grown tore_id IS NOT NULL
AND grown tore_id IN (190, 192)
AND o. chua reate_time & gt; 1483200000
AND o. chua reate_time & lt; 1513958399
AND (
O.p ay_status=2
The OR o.p ay_status=3
)
AND grown n NOT LIKE '% SO10 %'
AND grown n NOT LIKE '% SO20 %'
GROUP BY
I.o rder_id
The ORDER BY
Create_time DESC;

Explain the results


Now the query time is 8 seconds, can you tell me how to optimize

CodePudding user response:

1, considering the amount of data table
2, how much consider table field
More than 3, a table, ready to field index

CodePudding user response:

I have to talk nonsense again, what are you going to see this kind of statement scenario:

1. If it is a high concurrency, high Internet traffic, write the SQL, should have a good review,

2. If the report is, 8 seconds, is the ability to accept,

Say so many, I give some Suggestions:

1. If this is the Internet application,

First: the execution plan, good degree of differentiation to add appropriate indexes,

The second: the application as a service, to give to the database are simple query, (this is also a business of SQL optimization)

2. If the report is, can be in the big data calculation, if there is no large data set, it doesn't matter, at least in CongKu report on (library), don't crush the main library,

Thinking: think about how many such SQL, also like the above steps to solve it,

finally: personal WeChat public "andyqian" update many MySQL related articles, hope to help you!

CodePudding user response:

refer to the second floor u010695794 response:

I have to talk nonsense again, what are you going to see this kind of statement scenario:

1. If it is a high concurrency, high Internet traffic, write the SQL, should have a good review,

2. If the report is, 8 seconds, is the ability to accept,

Say so many, I give some Suggestions:

1. If this is the Internet application,

First: the execution plan, good degree of differentiation to add appropriate indexes,

The second: the application as a service, to give to the database are simple query, (this is also a business of SQL optimization)

2. If the report is, can be in the big data calculation, if there is no large data set, it doesn't matter, at least in CongKu report on (library), don't crush the main library,

Thinking: think about how many such SQL, also like the above steps to solve it,

finally: personal WeChat public "andyqian" update many MySQL related articles, hope to help you!

Thank you, this is indeed a report

CodePudding user response:

reference 1st floor splendid_java response:

1, considering the amount of data table
2, how much consider table field
More than 3, a table, ready to field index

Well ~ ~ ~, is to consider, but couldn't consider

CodePudding user response:

This is like the upstairs, according to your actual business scenarios to consider,

The update frequency is high, a large quantity of data table, at the start table when should consider whether or not split, now, of course, can also be split,