Querying case-insensitive data in MySQL often uses subqueries and uses the upper function in a subquery to convert the condition to uppercase. Such as:
The code is as follows:
SELECT * FROM Staticcatalogue WHERE UPPER (source) in (select UPPER (source) to 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 to use the function in the query condition, this will greatly reduce the query speed, if the query 100,000 data within 10 minutes can also obtain data, if it is a query hundreds of thousands of, will directly run the server dead, at this time can pass a temporary table, and add index, and then query. This can improve a lot of speed.
The code is as follows:
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;
This is the entire content of the SQL optimization program, I hope you can enjoy.