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

This article is to share with you about MySQL on In optimization is how, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, say no more, follow the editor to have a look.

MySQL version: 5.6.14

The files table records the file information in the dfs system.

There is an error in a batch of data upload and needs to be re-uploaded.

The scope of the error file has been recorded in the test.files_20170206 table.

Run the following query, but there is no result for a long time.

Select * from files T1 where (oldpath,flen) in (

Select oldpath,max (flen) from files f where oldpath in

(select oldpath from test.files_20170206)

Group by oldpath

)

Use explain extended to view the execution plan

The original SQL uses the Exists method.

Rewrite SQL as follows, which is actually adding a layer of nesting.

Select * from files T1 where (oldpath,flen) in (

Select * from (

Select oldpath,max (flen) from files f where oldpath in

(select oldpath from test.files_20170206)

Group by oldpath

) a

)

After rewriting, it meets the original expectations and saves the results as a temporary table. Then check the data again through the temporary table.

The above is how MySQL optimizes In. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.