It does not matter how far the road of life is, the top priority is to cherish every second of life; No matter how bad things get, something good is just around the corner

MySql when large amount of data, some people say that you need to add the index, and query data paging, but I want to say is not just these, such as the index, index of what kind of effect best, the principle of index to realize what kind of, a large quantity of data when I want to query a maximum value And so on a series of details.

  • Do you know how MySQL selects indexes when there are multiple indexes in MySQL?
  • Do you really use string indexes in MySql?
  • Should MySql select normal index or unique index?
  • Do you know how to gracefully use prefix index, index push down to optimize query speed?
  • Do you know how many tree searches are performed and how many rows are scanned? MySql > alter database alter database
  • MySql > select * from ‘MySql’; MySql gossip

If you are interested, you can follow the public account Biglead

The main content of this article is to query and obtain the maximum value of each group according to the user ID, as shown in the figure below

In an ordinary score record table, I found three items of data that met the requirements. Of course, this is for a user. If I need to obtain the record of the maximum value of task_correct among the three items, we can write it like this

SELECT
	a.user_name,
	a.task_num,
	a.task_correct,
	a.integral_num,
	a.integral_recoder_remark,
	a.screate_time 
FROM
	(
	SELECT
		COUNT(*),
		id,
		max( task_correct ) task_correct 
	FROM
		t_integral_recoder AS q 
	WHERE
		q.sta_mine_id = 54 
		AND q.category_one = "E0B2E1F8-498E-44" 
		AND q.category_two = "488863BE-9F55-4C" 
		AND q.user_name ="User name"AND q.screate_time BETWEEN "2021-06-01" AND "2021-07-31" 
	GROUP BY
		q.sta_user_id 
	) b
	INNER JOIN t_integral_recoder a ON a.id = b.id
Copy the code

The query result is as follows