---
slug: "mysql-slow-query-but-indexed-range-checked-for-each-record"
title: "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'\""
description: "Set up PHP remote debugging with XDebug and an IDE — `xdebug.remote_host` / `remote_port` plus an SSH tunnel pattern."
url: "https://www.ytyng.com/en/blog/mysql-slow-query-but-indexed-range-checked-for-each-record"
publish_date: "2024-07-24T11:30:46Z"
created: "2024-07-24T11:30:46Z"
updated: "2026-05-11T13:21:33.415Z"
categories: ["MySQL"]
keywords: ""
featured_image_url: "https://media.ytyng.com/resize/20250609/ebdb0d4bae264592a4862d9cb26ec9d6.png.webp?width=768"
has_video: true
has_music: true
video_urls: ["https://media.ytyng.net/ytyng-blog/313/featured-video-1.mp4", "https://media.ytyng.net/ytyng-blog/313/featured-video-2.mp4", "https://media.ytyng.net/ytyng-blog/313/featured-video-3.mp4"]
music_urls: ["https://media.ytyng.net/ytyng-blog/313/featured-music-313-1.mp3", "https://media.ytyng.net/ytyng-blog/313/featured-music-313-2.mp3"]
lang: "en"
---

# 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'"

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.