MySQL query optimization using custom variables

Optimizing sorting queries

An important feature of custom variables is that you can assign the result of mathematical calculations to the variable at the same time, similar to our i = i + 1 method. Here is an example for calculating the row number of a data table:

SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS rownum
FROM sakila.actor LIMIT 3;

The result may seem meaningless, because the primary key is incremented from 1, so the row number and the primary key value are the same. However, this method can be used for sorting. For example, if you need to query the top 10 actors who have played the most movies, the usual approach is to write it like this:

SELECT actor_id, COUNT(*) as cnt
FROM sakila.film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10;

The result may seem meaningless, because the primary key is incremented from 1, so the row number and the primary key value are the same. However, this method can be used for sorting. For example, if you need to query the top 10 actors who have played the most movies, the usual approach is to write it like this:

SELECT actor_id, COUNT(*) as cnt
FROM sakila.film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10;

If we want to obtain the corresponding ranking value, we can introduce variables to complete it:

SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
SELECT actor_id,
	@curr_cnt := cnt AS cnt,
  @rank := IF(@prev_cnt <> @curr_cnt, @rank+1, @rank) as rank,
  @prev_cnt := @curr_cnt AS dummy
FROM (
  SELECT actor_id, COUNT(*) AS cnt
  FROM sakila.film_actor
	GROUP BY actor_id
	ORDER BY cnt DESC
	LIMIT 10
) as der;

Here, the number of movies played is assigned to the curr_cnt variable, and prev_cnt is used to store the number of roles played by the previous actor. The ranking starts from the first place. If the number of actors behind is different from the number of actors in the previous place, the ranking will go down (+1). If they are the same, the ranking will be the same as the previous actor. In this way, the actor's ranking can be obtained directly from the query results without the need for secondary processing from the database query (of course, this can also be achieved through program code).

Avoid repeatedly obtaining the data row that has just been modified

If you want to re-acquire the information of the data row when updating the data row, you often need to read the database again. This is because MySQL does not return updated data rows at the same time like PostgreSQL's UPDATE RETURNING function, but only returns the number of rows affected by the update. However, we can do this with custom variables. For example, to get the row whose update time has just been modified, an additional query is required without using custom variables:

UPDATE tb1 SET lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM tb1 WHERE id = 1;

This can be avoided by using custom variables:

UPDATE tb1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;

Although there is still a query operation, subsequent query operations no longer need to access the database.

Lazy loaded join query

Suppose we need to write a union query to complete the following task: find matching data rows on the branches of the union, and skip other branches if found. This happens when you need to look up hot data or infrequently accessed data (such as recent orders and historical orders). Here is a common SQL for user query below:

SELECT id FROM users WHERE id = 123
UNION ALL
SELECT id FROM users_archived WHERE id = 123;

This query will first query the user with id 123 from the currently used user table, and then find the user with the same id from the archived user table. However, this writing method is inefficient. Even if the desired user is found in the users table, it is still necessary to search again in the users_archived table. The actual user ID 123 will only exist in one of the tables or the data in the two tables are the same. This situation can be avoided by using lazy-loaded join queries - the second branch is queried only when no data is found in the first branch. Therefore, you can use MySQL's GREATEST method as a container for query results to avoid returning multiple data columns.

SELECT GREATEST(@found := -1, id) AS id, users.name, 'users' as which_tb1
FROM users WHERE id = 123
UNION ALL
	SELECT id, users_archived.name, 'users_archived'
  FROM users_archived WHERE id = 123 AND @found IS NULL
UNION ALL
	SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL;

If the first row of the above query has results, @found will not be assigned a value and will be NULL, thus executing the second query. The third UNION actually has no effect. It is just to restore @found to NULL so that this SQL can be executed repeatedly. Another way to verify is to perform such an operation on the same table and find that only one row of data is actually returned or no data is returned (when no data is found).

SELECT GREATEST(@found := -1, `id`) AS `id`, `infocenter_city`.`name`, 'city' as which_tb1 
FROM `infocenter_city` WHERE `id` = 460100 
UNION ALL 
	SELECT `id`, `infocenter_city`.`name`, 'infocenter_city' 
	FROM `infocenter_city` WHERE id = 460100 AND @found IS NULL 
UNION ALL 
	SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL

The above is the details of MySQL using custom variables for query optimization. For more information about MySQL using custom variables for query optimization, please pay attention to other related articles on 123WORDPRESS.COM!