Using custom variables in MySQL to write a lazy UNION example bitsCN.com
(Refer to < <High-performance mysql> >)
Assume that a UNION query is required. The first subquery is run as the branch first. if a matched row is found, the query of the second branch is no longer executed.
In general, we can write such a UNION query:
Select id from users where id = 123456
Union all
Select id from users_archived where id = 123456;
This query can run normally. However, no matter whether a record is found in the users table, it is scanned once in the users_archived table. Therefore, duplicate records may be returned. To reduce unnecessary overhead in this case, the SQL statement can be written as follows:
Select greatest (@ found: =-1, id) AS id, 'users' AS which_tbl
FROM users WHERE id = 1
UNION ALL
SELECT id, 'users _ archived'
FROM users_archived WHERE id = 1 and @ found IS NULL
UNION ALL
SELECT 1, 'reset' from dual where (@ found: = NULL) is not nll;
The preceding query uses the custom variable @ found. by assigning a value in the Result column and placing it in the GREATEST function, extra data is not returned. If the first branch query result set is NULL, @ found is still NULL, so the second branch query is executed. In addition, in order not to affect the subsequent traversal results, @ found is reset to NULL at the end of the query.
In addition, the second column of returned data is used to indicate whether the record is queried in the users table or the users_archived table.
BitsCN.com