- 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.