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

This article mainly explains the "SQL search how to optimize", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "SQL search how to optimize" it!

The way most people write it at present

After several times of REVIEW code, such phenomena are found as follows:

In the business code, you need to query whether there are records based on one or more conditions, regardless of how many records there are. The common SQL and code writing methods are as follows: SELECT count (*) FROM table WHERE a = 1 AND b = writing Java: int nums = xxDao.countXxxxByXxx (params); if (nums > 0) {/ / when it exists, execute the code here} else {/ / when it does not exist, execute the code here}

Does it feel very OK? there is no problem.

Optimization scheme

It is recommended to write as follows:

# SQL: SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT Java: Integer exist = xxDao.existXxxxByXxx (params); if (exist! = NULL) {/ / when it exists, execute the code here} else {/ / execute the code here when it does not exist

SQL no longer uses count, but uses LIMIT 1 instead, so that when a database query comes across, it will be returned, and do not continue to find how many entries are left.

COUNT () has two very different functions: it can count the number of values in a column, or it can count the number of rows. Column values are required to be non-empty when counting column values (NULL is not counted). If you specify a column or column expression in parentheses in COUNT (), you count the number of results that the expression has value. Another function of .count () is to count the number of rows in the result set. When MySQL confirms that the expression value in parentheses cannot be empty, it is actually counting the number of rows. The simplest thing is that when we use COUNT (*), the wildcard character * does not extend to all columns as we guessed. In fact, it ignores all columns and counts all rows directly.

Thank you for your reading, the above is the content of "SQL search how to optimize", after the study of this article, I believe you have a deeper understanding of how to optimize SQL search, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.