SELECT orders.GUIZI_NO AS cabNo, CAST(SUM(ordinPost.amount) AS DECIMAL(14,2)) AS orderMoney FROM ( SELECT order_water_code, amount FROM wallet_pay_post_record t WHERE t.create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND t.`status` = 1 ) ordinPost INNER JOIN guizi_order orders ON ordinPost.order_water_code = orders.TRADE_WATER_NO GROUP BY orders.GUIZI_NO HAVING orderMoney < '200.00'
wallet_pay_post_record This table has more than 100 million pieces of data, and the query statistics are slow. Asking create_time to add an index doesn’t seem to improve much speed either.
1 In the inner layer, the wallet_pay_post_record table is operated by group according to the order_water_code field to reduce the amount of data in INNER JOIN
2 If wallet_pay_post_record and guizi_order are many-to-one relationships, the amount & lt; 200 condition can be moved to the inner layer to deal with first.
SELECT order_water_code, sum(amount) as amount FROM wallet_pay_post_record t WHERE t.create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND t.`status` = 1 and t.amount < 200 group by order_water_code having amount < 200
Single table 100 million, sub table should be.
Another single table 100 million, you just take the last 30 days of the record, 30 days, how many records? If you take up 100/10 of the total, the efficiency of indexing will be good.
If the index is fixed, you can compose the index (status, create_time), otherwise you can have a create_time single-column index.
Finally, orders.TRADE_WATER_NO must be indexed.
Optimization suggestion: do not operate in SQL.
Using space to change time, you can write multiple SQL statements separately.
1.Modify DATE_SUB, use create_time> = ‘…’ and create_time < = ‘…’.
2.orderMoney < ‘200.00’ Change to orderMoney < 200.
It is better to send out explain information.
Do not use database function.