SQL determines whether it exists. Is it still using count operation? Very time consuming!

Time:2021-9-8

Query yes and no from the database table according to a certain condition. There are only two statuses, so why select count (*) when writing SQL?

Whether it’s a new programmer or Lao Bai, a programmer who has been on the battlefield for many years, they are always count

The way most people write at present

When reviewing the code several times, the following phenomena are found:

In the business code, you need to query whether there are records according to one or more conditions, regardless of how many records there are. The common SQL and code writing methods are as follows

SQL writing method:


SELECT count(*) FROM table WHERE a = 1 AND b = 2

Java writing:

int nums = xxDao.countXxxxByXxx(params);
if ( nums > 0 ) {
 //Execute the code here when it exists
} else {
 //When it does not exist, execute the code here
}

Does it feel OK, no problem

Optimization scheme

The recommended wording is as follows:

SQL writing method:


SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

Java writing:

Integer exist = xxDao.existXxxxByXxx(params);
if ( exist != NULL ) {
 //Execute the code here when it exists
} else {
 //When it does not exist, execute the code here
}

SQL no longer uses count, but uses limit 1 instead. When querying the database, it will return one item. Don’t continue to find how many items there are

You can directly judge whether it is not empty in the business code

summary

The more entries found according to the query criteria, the more obvious the performance improvement. In some cases, you can also reduce the creation of joint indexes.

In this article about whether SQL determines “existence”, are you still using count operation? Very time consuming! That’s the end of the article. For more information about whether SQL judgment “exists”, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you can support developeppaer in the future!