SQL Logic optimization case when converted to union ALL

In general, database optimization can be divided into 4 aspects from the hardware level:

CPU: That is, reduce the computational complexity, such as reducing the various types of SQL aggregation functions, window functions, case when and so on.

IO: (Less access to data during query result set.) Data optimization from here to a great extent

Network: Less query result set size, eliminate unnecessary query fields

Database resources: The database resources are mainly the parameter settings of the data, such as index, data cache. Contention for locks, deadlocks, locks, etc. Most of the lock problems are optimized from the business logic. such as splitting transactions, reducing transaction complexity and the association of tables in transactions. Do a few more commits. Let the transaction complete as soon as possible, freeing up resources. On the other hand, depending on the business situation, use a read lock that satisfies the requirements of the low isolation level. Use the same order as possible for the operations of the library table in the business. such as similar business Transactions first query T1 table and then query T2 table. (try to avoid using replace into in MySQL.) , insert INTO ... on dumplicate .... Deadlock is prone to concurrency)

Example:

SQL logic rewrite to reduce CPU and IO usage:

Case when some cases are converted to union ALL

Shorthand

SELECT * FROM (
....
(case is A.updatetime>b.updatetime then a.updatetime ELSE b.updatetime END) as UpdateTime,

From a
Left JOIN b on b.clientid = A.clientid
) T
WHERE t.updatetime>= ' xxxxx '

Rewrite:

SELECT ....
A.updatetime as UpdateTime,
From a

Left JOIN b on b.clientid = A.clientid

WHERE a.updatetime>= ' xxxx ' and a.updatetime>b.updatetime
UNION All
SELECT ....
B.updatetime as UpdateTime,
From a
Left JOIN b on b.clientid = A.clientid
WHERE b.updatetime>= ' xxxx ' and a.updatetime<=b.updatetime

SQL Logic optimization case when converted to union ALL