Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "NOT EXISTS Optimization case course in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. the original statement:

SELECT * FROM dcf_account.t_posting_transaction T1 WHERE NOT EXISTS (SELECT * FROM dcf_loan.t_account_posting_detail T2 WHERE t1.track_no = t2.posting_num)

Second, some thoughts on optimizing and changing sentences:

For the original statement, because it is a NOT EXISTS judgment, there is no need for SELECT in the subquery, just SELECT ID or even SELECT 1 is fine. In addition, in view of the poor performance of the MySQL subquery algorithm, consider rewriting it to the corresponding JOIN mode. Because this is the NOT EXIST judgment, there is some skill in rewriting, that is, you can use the left join, and then filter out the records that failed to connect. In addition, the outermost query of the original statement is also SELECT. As to whether this is necessary or not, we should take as few fields as possible according to the specific business requirements. Finally, with the above optimizations made immediately, the SQL performance will still be very poor if there is no suitable index on the join conditions (especially when the table has a large amount of data), so consider creating indexes on the join condition columns t1.track_no and t2.posting_num respectively.

3. The final optimization results are as follows:

The running time of the statement is reduced from one hour to one second.

SELECT t1.id FROM dcf_account.t_posting_transaction T1 LEFT JOIN dcf_loan.t_account_posting_detail T2 ON t1.track_no = t2.posting_num WHERE t2.id IS NULL; "NOT EXISTS optimization case tutorial in MySQL" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.