12

Does anyone have any experience with performance of polymorphic relationships in Laravel? I need to have a settings tables for few different models, polymorphic relationship would be good here, I am wondering about performance, is it as good as if each model had its own settings table?

Settings table is simple and this is how the settings table would be like:

$table->increments('id');
$table->integer('foreign_id')->unsigned();
$table->string('key');
$table->string('value')->nullable();

I would shave off few tables that are needed for each models settings, just wondering if there would be performance issues using polymorphic setup?

If I decide to go with polymorphic then I would add these types to table:

        $table->string('has_settings_type');
        $table->biginteger('has_settings_id')->unsigned();

That's what laravel requires to add support for polymorphic relationships.

niko craft
  • 2,893
  • 5
  • 38
  • 67
  • you're probably a bit ambitious using biginteger for your foreign keys, just integer should be fine – Thijs Steel Jul 27 '17 at 11:26
  • 1
    Wordpress uses biginteger. I am making a CMS so I want to be safe rather then thave to change it later down the road – niko craft Jul 27 '17 at 11:27
  • also biginteger is used because the model uses bigincrease and then you have to use biginteger to be able to create foreign key. – niko craft Jul 27 '17 at 11:27

3 Answers3

13

Performance won't be a problem with polymorphic relations as long as you add the correct indexes to your table.

Your table, however, is missing a field to make it polymorphic, you need the name of the model that is related to that setting.

$table->increments('id');
$table->integer('foreign_id')->unsigned();
$table->string('foreign_object');                       // Object name (e.g.: 'App\User')
$table->string('key');
$table->string('value')->nullable();

$table->index(['foreign_id', 'foreign_object']);        // To get all settings for an object
$table->index(['foreign_id', 'foreign_object', 'key']); // To get single key for an object
Jerodev
  • 32,252
  • 11
  • 87
  • 108
  • BTW those indexes you created, how would they help, get all settings for an object or get a single key? Can you show example? And indexes will speed everything up? – niko craft Jul 27 '17 at 11:19
  • a compound index like the ones mentioned is a data structure that allows efficient queries to be performed on combinations of these attributes. So you can just do the queries like you normally do and the database will do the rest. – Thijs Steel Jul 27 '17 at 11:22
  • i also recommend adding foreign key constraints, just to be safe. For settings, cascase is probably best – Thijs Steel Jul 27 '17 at 11:22
  • 1
    Hi Steel you mean cascade on update/delete? I do not think you can use foreign key constraints on polymorphic setup. – niko craft Jul 27 '17 at 11:24
  • just to understand better the index above will make this Setting::where('foreign_id', 1)->where('foreign_object', 'App\Abc')->first() much faster? Is that correct syntax I used or do I have to query in another way to get the speed from indexes? – niko craft Jul 27 '17 at 11:26
  • oh right, you can't do foreign key on polymorphic, had it confused with composite – Thijs Steel Jul 27 '17 at 11:30
  • How about the other part of question? Did I understand it correctly? compound indexes above will make this Setting::where('foreign_id', 1)->where('foreign_object', 'App\Abc')->first() much faster? – niko craft Jul 27 '17 at 11:34
  • https://stackoverflow.com/questions/795031/how-do-composite-indexes-work if you're interested. – Thijs Steel Jul 27 '17 at 11:50
  • What if someone changes models directory from `App/...` to `App/Models/...` I think fully qualified name should not be saved – Afraz Ahmad Jan 11 '20 at 11:04
  • @Afraz Ahmad In that case you should use a morphmap https://laravel.com/docs/6.x/eloquent-relationships#custom-polymorphic-types – IGP Jan 16 '20 at 19:29
  • FYI, you can use `$table->morphs('foreign_object');` which will create the _type (instead of _object) and _id column as well as the composite index for you. – fylzero Nov 22 '22 at 16:57
5

An improvement to Jerodev's answer. Index order matters a lot.

Usually you would want to search for something like this
get all settings for a given model (foreign_object)

if you keep the index order like this (foreign_id,foreign_object), then sql will not be able to use index in order to execute the above query because the leftmost column in the index is not foreign_object

Thus the indexs should go like this

$table->index(['foreign_object', 'foreign_id']);
$table->index(['foreign_object', 'foreign_id', 'key']);
Ravish
  • 119
  • 3
  • 8
2

If you are using less than 5.2.34 there is no eager loading of relations past the polymorphic one. You can read more about this issue here. An example would be if images was polymorphic and you did:

->with('product.images.sources');

sources would not be eager loaded.

Alex Harris
  • 6,172
  • 2
  • 32
  • 57