MySQL 700000 data volume query optimization, ask for advice

encounter such a scenario in the actual business:

there are three tables wf_cr_borrow_order, wf_admin_last_check_record_content and wf_cr_replan, and the amount of data is wf_cr_borrow_order (755424), wf_admin_last_check_record_content (54253) and wf_cr_replan (321744), respectively. The three tables are linked by the order number, enter the function list and hold the data of the successful wf_cr_borrow_order.br_state= loan and the wf_cr_borrow_order.repayment_state in (overdue, overdue payment) of the three tables, and then display them in reverse order according to wf_admin_last_check_record_content.create_time,wf_admin_last_check_record_content.update_time,wf_cr_borrow_order.update_time. The list shows 25 items, the query time is about 3 seconds +, and the total number of count data items is about 0.9 seconds +.

the question for advice is:

1. The result of explain is how does Using where; Using temporary; Using filesort, avoid file sorting?

Solutions you have tried

1. The corresponding fields of the associated table are indexed.
2. It is certain that if the sorting of the two fields of wf_admin_last_check_record_content.create_time,wf_admin_last_check_record_content.update_time is removed, the query speed and the rows returned by explain will be significantly improved. But the business needs can not be removed!

currently used query SQL: 

EXPLAIN
SELECT
    cbo.br_order_no,
    cbo.loan_date,
    cbo.replan_repay_time,
    cbo.final_settle_date,
    cbo.penalty_money,
    cbo.applied_amount,
    cbo.br_inst,
    cbo.repay_total_default,
    cbo.cut_total,
    cbo.br_syje,
    cbo.br_txje,
    cbo.real_repayment_money,
    cbo.br_state,
    cbo.repayment_state,
    cbo.customer_id,
    cbo.customer_version,
    cbo.param2,
    cbo.collect_id,
    cbo.product_id,
    r.total_penalty_days
FROM
    `wf_cr_borrow_order` AS cbo
LEFT OUTER JOIN wf_admin_last_check_record_content c ON cbo.br_order_no = c.br_order_no
LEFT OUTER JOIN wf_cr_replan r ON cbo.br_order_no = r.br_order_no
WHERE
    (
        cbo.`br_state` = 8
        AND cbo.`repayment_state` IN ("3", "4")
    )
ORDER BY
    c.id,
    c.create_time,
    cbo.`last_update_time` DESC
LIMIT 0,
 25;

result from Explain:

desired results:

1. If you can optimize on the basis of the existing SQL, you want to optimize the file sorting.
2. If there is no room for optimization in the existing SQL, please point out that there are any other optimization solutions or solutions for this scenario.
I hope all the bosses will not hesitate to give us advice [holding fists]

Mysql
Nov.11,2021

it is not recommended to write complex associated SQL queries in business code for the following reasons:
1 complex SQL maintenance is difficult to read
2 complex SQL performance is poor
3 complex SQL results generally cannot be cached

very often, when you don't have to associate queries, simple SQL, splices data in the business code, and there are a lot of
1 SQL that are easy to maintain. You are sure that the SQL results you write are not correct.
2 SQL results can be cached, or cached to implement simple
3 to avoid poor performance of complex queries
4. If business split is involved in the future, the splitting difficulty will also become smaller
5 simple query index will be easy to hit. Add a better index according to the actual situation

back to your case, you can switch to a simple query. All three queries can hit the index. After rewriting to a simple SQL, your problem complexity is greatly reduced

.

I have recently made some optimizations on join queries, because my order of magnitude is large, tens of millions of orders of magnitude, so direct join will be very slow.
so it's much faster to use a subquery to Filter the results of the main table before joining it. However, the effect of this optimization method also depends on the specific needs, if the sub-table out of the conditional Filter is still very large, the optimization effect is relatively poor.

does not know which field your index is based on, so it is difficult to judge how to optimize it. In fact, generally speaking, type is already very good in ref.
there is also a point that the number of scanning rows in cbo is 34 wks. I think your time is here.
another possibility is that it is stuck on the io of the system. The data is out but needs to be filtered, so it is slow. If this is the case, you can build multiple sql, select one piece of data per sql, and then splice it into 25 pieces of data
you can use the profiling command to see which time-consuming it is, and then analyze

do not know which field your index is built in, so it is difficult to judge how to optimize

.

wf_cr_borrow_order (755424), plus the following two Filter conditions, how many records are there?
cbo. br_state = 8
AND cbo. repayment_state IN ('3percent,' 4')
if there is still 300000 + data after Filter as shown in the execution plan, walking the index is better than scanning the whole table.

there is also a bit of optimization in writing, taking the associated statements of wf_cr_borrow_order and wf_admin_last_check_record_content (including where,order by,limit) as subqueries, and then associating them with wf_cr_replan.

Why use left outer join?

Previous: Py4j.protocol.Py4JError

Next: How js traverses an array of arbitrary levels at any depth and converts it into html fragments

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b3b05f-2c28b.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b3b05f-2c28b.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?