Here's the English translation of your blog title:"A Story About Trying to JOIN Fields with Different Collations in MySQL and Getting 'Range checked for each record'"
2024-07-24 20:30 (4 months ago)
ytyng
I encountered an issue in MySQL where a subquery on a field with an index takes an enormous amount of time because the index is not being utilized.
Even when I tried to force the use of the index with FORCE INDEX, it did not apply.
When I checked with EXPLAIN, although the index appeared in possible_keys, the key was NULL, indicating that it wasn't being used.
The rows column showed the total number of records, and an unfamiliar message appeared in the EXTRA column:
Range checked for each record (index map: 0x10)Upon revisiting the table definition, I discovered that the collation of the fields was different.
Specifically, the character sets were different: I was comparing utf8mb3 (COLLATE utf8mb3_general_ci) with utf8mb4 (COLLATE utf8mb4_bin).
In this scenario, it is inherently impossible to use the index for the search.
Thus, the only solution was to align one of the fields using ALTER TABLE CHANGE COLUMN.
Since it was an ID field, I aligned it to utf8mb3.
ALTER TABLE new_table
CHANGE COLUMN some_id some_id varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
CHANGE COLUMN next_some_id next_some_id varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL;However, it's generally not advisable to align to utf8mb3 due to its lack of future support, and deprecation warnings do appear. But in this case, it was unavoidable.
[HY000][1287] 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead
[HY000][3778] 'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.Moreover, although utf8mb3_bin would theoretically be more efficient than utf8mb3_general_ci for collation, I aligned with utf8mb3_general_ci to match the other fields.
Comments