I have a simple query where I am trying to query for matching records with a where like % that’s causing Out of sort memory error

The query in question looks like this:

select *
from `collections`
where (`policy_id` like '%apple%' or `name` like '%apple%')
order by LOCATE('apple', `name`)
limit 10 offset 0;

So I am searching for the term apple in the collection table (with approx 80k rows) and using order by LOCATE to return the most closest match to the top.

I did a quick check to see how many results were matching without using the order by and limit, and there were only 16 rows

Any idea why this particular query is resulting in Out of sort memory error?

The database is version is MySQL 8 (RDS), and the table schema is:

create table collections
    id                 bigint unsigned auto_increment   primary key,
    policy_id          varchar(64)                      not null,
    name               varchar(255)                     not null,
    constraint         collections_policy_id_unique unique (policy_id)
    collate = utf8mb4_unicode_ci;

create index collections_name_index
    on collections (name);

Khám phá các thẻ bài đăng