MySQL queries duplicate (case insensitive) data for SQL optimization

Querying for data that is not case-sensitive in MySQL often uses subqueries and uses the upper function in a subquery to convert the condition to uppercase. Such as:

SELECT * FROM Staticcatalogue WHERE UPPER (source) in (select UPPER (source) from Staticcatalogue GROUP by UPPER (source) hav ing 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 in the query conditions used in the function, which will greatly reduce the query speed, if the query 100,000 data within 10 minutes can also obtain data, if the query hundreds of thousands of, will be directly to the server to die, at this time can be through a temporary table, and indexed, and then query. This can improve a lot of speed.

CREATE TABLE staticcatalogue_tmp SELECT UPPER (source) as source from Staticcatalogue GROUP by UPPER (source) has count (U Pper (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;