Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 配置大小写不敏感字符集引起的性能下降
|
username: Hacker007
Figure 1 shows a case-sensitive character set, while Figure 2 is configured with a case-insensitive character set. The query times on both sides seem to differ significantly. What’s going on here?
Could you share the SQL? We have also configured case insensitivity.
After configuring case insensitivity, the date still needs to be formatted.
Let’s look at the execution plan. It’s easy to identify the issue through the execution plan.
I suspect it might be:
Implicit conversion causing the index to be skipped.
Not using the index, a full table scan would be much slower.
The left side of the inequality in the WHERE clause cannot use functions, it will result in a full table scan.
Check whether the character set and collation of the tables test_info, rent_test, and test_snapshot are consistent.
You only changed some fields of some tables, right? Confirm whether the field types and collation of the associated fields are consistent.
All consistent, all synchronized with the upstream MySQL.
Could you share the execution plans for the same SQL in these two scenarios?
It should be one using an index, and the other a full table scan.
Performance is an issue, and another major problem is the conversion of date formats.
Some query execution plans are different.
There is so much blank space on the right, just make each column a little wider. The first column is covered, and a lot of other information is also covered. This is an invalid response. 
The things we are concerned about, you don’t release them 
This way, we can’t help you…
It’s not just a performance issue now, but a syntax execution failure.
It looks like the response above indicates a failure in execution, which seems to be a bug.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: