SQL optimization solution for querying duplicate data in mysql, mysqlsql

SQL optimization solution for querying duplicate data in mysql, mysqlsql

When querying non-case-insensitive data in mysql, subqueries are often used, and the upper function is used in subqueries to convert conditions into uppercase. For example:

Copy codeThe Code is as follows:
Select * from staticcatalogue where upper (Source) IN (select upper (Source) FROM staticcatalogue group by upper (Source) having count (UPPER (Source)> 1) order by upper (Source) DESC;

The execution efficiency of this statement is very low, especially if the Source field is not indexed. In particular, the most taboo function is used in the query condition, which greatly reduces the query speed. If you query 100,000 pieces of data within 10 minutes, you can still get the data, if you want to query hundreds of thousands of records, the server will be directly killed. In this case, you can use a temporary table, add an index, and then query. This can increase the speed.

Copy codeThe Code is as follows:
Create table staticcatalogue_tmp select upper (Source) AS Source FROM staticcatalogue group by upper (Source) having count (UPPER (Source)> 1;
Alter table staticcatalogue_tmp add INDEX TX_1 (Source );
Select s. * from staticcatalogue s where upper (s. Source) IN (SELECT st. Source FROM staticcatalogue_tmp st) order by upper (s. Source) DESC;

The above is all about the SQL optimization solution in this article. I hope you will like it.