scopeOrderByTranslation really slow

[yooplait]

Hello,

When dealing with tables with a big number of translations (we're talking about around 1 million records), the query time is far too long.

Here are my tables:

Schema::create('herbs', function (Blueprint $table) {
    $table->id();
    $table->string('sciname')->nullable()->unique();
    $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
    $table->timestamps();
});

Schema::create('herb_translations', function (Blueprint $table) {
    $table->id();
    $table->string('locale')->index();
    $table->string('name')->index();
    $table->foreignId('herb_id')->constrained()->cascadeOnDelete();
    $table->unique(['herb_id', 'locale']);
    $table->unique(['herb_id', 'locale', 'name']);
});

The herbs table contains about 300,000 records and the translation table 1,000,000.

Imagine I want to get the first 10 herbs sorted by their name.

Herb::query()->orderByTranslation('name')->limit(10)->get()

Unfortunately, OrderByTranslation creates an issue here due to the ORDER BY clause and the LEFT JOIN (whereas with an INNER JOIN, it's significantly faster).

The INNER JOIN utilizes indexes, while the LEFT JOIN relies on file sorting (so bad performance).

Has anyone tested this method with large data sets?

Thank you in advance for your help,
Thanks again!