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

This article mainly introduces MySql how to use not in to achieve optimization, the article is very detailed, has a certain reference value, interested friends must read it!

Recently, not in was used in the select query on the project to exclude the unused primary key id. The sql that was initially used was as follows:

Select s.SORT_ID, s.SORT_NAME, s.SORT_STATUS, s.SORT_LOGO_URL, s.SORT_LOGO_URL_LIGHTfrom SYS_SORT_PROMOTE s WHERE s.SORT_NAME = 'must hear Classic' AND s.SORT_ID NOT IN ("SORTID001") limit 1

When there is more data in the table, the execution time of this sql is longer and the execution efficiency is low. Looking for information on the Internet, it is said that left join can be used for optimization. The optimized sql is as follows:

Select s.SORT_ID, s.SORT_NAME, s.SORT_STATUS, s.SORT_LOGO_URL, s.SORT_LOGO_URL_LIGHTfrom SYS_SORT_PROMOTE sleft join (select SORT_ID from SYS_SORT_PROMOTE where SORT_ID=# {sortId}) bon s.SORT_ID = b.SORT_ID WHERE b.SORT_ID IS NULL AND s.SORT_NAME = 'must hear Classic' limit 1

The sortId in the above SORT_ID=# {sortId} is passed into the SORT_ID field that needs to be excluded. For the left outer join, use the field to be filtered (SORT_ID) as the connection condition, and finally add b.SORT_ID IS NULL to the where condition to filter out the relevant data in the table.

The above is all the content of the article "how to optimize MySql with not in". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to 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.