• The way most people write it today
  • Optimization scheme
  • conclusion

SQL > SELECT count(*) from SQL > SELECT count(*);

When reviewing the code for many times, we found the following phenomenon: in the business code, we need to query whether there are records according to one or more conditions, regardless of how many records there are. Common SQL and code writing is as follows

Writing SQL:

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

Java write:

int nums = xxDao.countXxxxByXxx(params); If (nums > 0) {// If there is, execute this code} else {// If there is no, execute this code}Copy the code

Do you feel OK? There is no problem. The recommended optimization scheme is as follows:

Writing SQL:

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1
Copy the code

Java write:

Integer exist = xxDao.existXxxxByXxx(params); if ( exist ! = NULL) {// Execute this code if it exists} else {// Execute this code if it does not exist}Copy the code

LIMIT 1 = 1 LIMIT 1 = 1 LIMIT 1 = 1 LIMIT 1 = 1 LIMIT 1 = 1 LIMIT 1 = 1 LIMIT 1 = 1 LIMIT 1

conclusion

The more entries are found based on the query criteria, the greater the performance improvement and, in some cases, the reduction in the creation of federated indexes.