Slow Query Performance with MorphTo Relationships When model_id is a String but morphed relationship has integer as primary key

[RRosalia]

Laravel Version

11.37.0

PHP Version

8.4.2

Database Driver & Version

MySQL 8.0.39 AWS RDS (db.m7g.8xlarge)

Description

Description

When using MorphTo relationships with a {name}_id defined as a string data type queries perform significantly slower when the model that is morphing has an integer primary key.

(Because i want to support both integers and uuid values in the morph relation)

This issue arises because MySQL does not utilize indexes effectively when there's a type mismatch between the query parameter and the column data type. The table that has this issue has 142M rows inside of it. Now as a workaround we use the DB Facade to run the query raw but having a method inside the framework to just cast for morph relations the type to string would be very beneficial.

Requested behaviour

The behaviour as it is now is expected behaviour since our primary key of the product table is indeed an integer. Is there a way we can force this integer to be a string? In mysql the issue lies in the fact that indexes are not been used when the data types don't match.

Steps To Reproduce

Steps to reproduce

class User extends Model
{
    use HasUuids;

    public function media()
    {
        return $this->morphMany(Media::class, 'model');
    }
}

class Product extends Model
{
    public function media()
    {
        return $this->morphMany(Media::class, 'model');
    }
}

class Media extends Model
{
    use HasFactory, SoftDeletes;

    public function model(): MorphTo
    {
        return $this->morphTo();
    }
}

1. Retrieve media for User:

$user->media()->get();

select * from `media` 
where `media`.`model_id` in ("6c7f35d9-39c0-4350-8966-011089e3a4b3") 
  and `media`.`model_type` = 'App\\Domain\\Models\\User' 

Performance: Runs very fast (16 milliseconds).

2. Retrieve media for Product:

$product->media()->get();

select * from `media` 
where `media`.`model_id` in (873542) 
  and `media`.`model_type` = 'App\\Domain\\Models\\Product' 

Performance: Takes approximately 8 seconds.

If the above query would be

select * from `media` 
where `media`.`model_id` in ('873542') 
  and `media`.`model_type` = 'App\\Domain\\Models\\Product' 

Performance: Runs very fast (16 milliseconds).

[macropay-solutions]

Have you tried setting $keyType = 'string' in your model to see how it behaves?

[RRosalia]

I will than get another problem whereby from Product my id will always be a string everywhere and i need to typecast is back to integer as fundamentally it is.

The idea is just to be able to use the same polymorphic table for all models without compromising speed in this case. On local its fast as there is almost no data in production i really need the index thus its necessary to be able to somehow for polymorphic relationships to typecast something on query run time.

[macropay-solutions]

See this similar issue #53515 (comment)

try this #53515 (comment)