Column order in index for Blueprint morphs

[taai]

Hi,

As I have learned, the order of columns in multi-column index matters. The most unique columns should go first. Here is an article about that: https://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys

Here is an example of a migration:

Schema::create('media', function (Blueprint $table) {
    $table->bigIncrements('id');

    $table->morphs('model');
});

The method morphs() calls the method numericMorphs() which looks like this:

public function numericMorphs($name, $indexName = null)
{
    $this->string("{$name}_type");
    $this->unsignedBigInteger("{$name}_id");

    $this->index(["{$name}_type", "{$name}_id"], $indexName); // <-- change this!
}

I suggest the {$name}_id column to go before the {$name}_type column in that index. 🤔

// like this
$this->index(["{$name}_id", "{$name}_type"], $indexName);

[Sophist-UK]

Sorry, but I think your suggestion is wrong and the current code is correct.

It makes no sense to do SELECT * FROM MEDIA WHERE MODEL_ID=1234 because the whole point of polymorphic tables is that the TYPE field needs to be specified.

It makes some sense to do SELECT * FROM MEDIA WHERE MODEL_TYPE="MEDIA" though I cannot imagine when you might do this.

I think you have mis-interpreted the use the index article because the current code seems to me to be following the advice given there.

[taai]

I think you misunderstood. I'm talking about Laravel Eloquent polymorphic relationships. That index is used when selecting records by a polymorphic relationships; and a pair of columns is used - model type and model id. Here is a query that Laravel generates when fetching records by a polymorphic relationships:

$post->media()->toSql();
// returns
// select * from "media" where "media"."model_type" = ? and "media"."model_id" = ? and "media"."model_id" is not null

In this case model_type would be App\Models\Post and model_id would be the value for the id column in posts table.

And, if I have understood correctly how B-Tree indexes work... Imagine that there are millions of records and around 10 "types" (let's call them "folders"). If I'm asking for the records that matches the type "post" and the id "123", a folder "post" is going to be found and it would be HUGE and it would take long time to scan through the records in it for the match of id... But if we make "folders" by ID's, then there will be "shelves" of millions of categorized, very thin folders and the search and scan is going to be fast.
In another words... First the database will look in the index and search for value App\Models\Post - it can be millions of index nodes/leafs (matches) and only then search for the ID... But this means that the database (PostgreSQL in this case) could decide not to use the index at all because of how inefficient the index ha become, because how "tall" the tree would have become. But if the first column in the index would be the ID, then the B-Tree index is going to be "wide" and efficient.

Here are some better articles:
https://medium.com/trendyol-tech/postgresql-multicolumn-b-tree-i%CC%87ndekslerde-column-s%C4%B1ras%C4%B1n%C4%B1n-%C3%B6nemi-6ca76043a6c
https://www.pgmustard.com/blog/2019/05/20/multi-column-indexes

[Sophist-UK]

No - I understand what polymorphic relationships are and how they work. And I understand how SQL indexes work and understand the article you referred to.

In your example, where you are looking up a specific morph record, both id and type are specified, so the performance question comes down to how many leaf nodes need to be read from disk to find the pointer to the actual record. According to https://use-the-index-luke.com/sql/anatomy/the-tree , The structure is a balanced search tree because the tree depth is equal at every position; the distance between root node and leaf nodes is the same everywhere. In other words, you need to read the same number of nodes to find a single record regardless of the order of the index.

But if you need to read the locations of all the records of a specific type then if you put {name}_id first, you need to read all of the index nodes, whereas if you put {name}_type first, you will only need to read those that contain that specific type.

To give you a real example, if you have a blog and you allow comments on (say) posts and images and videos, so the comments table has parent_id and parent_type, then if you wanted to list all the comments for related to videos, you could do a query such as SELECT * FROM comments WHERE parent_type = "video";. It is extremely unlikely that you would want to select comments relating to a parent_id which is a specific integer across posts, images and videos because there is no real-life relationship between posts, images and videos which happen to have the same id.

[Sophist-UK]

To correct the previous comment, when I said:

But if you need to read the locations of all the records of a specific type then if you put {name}_id first, you need to read all of the index nodes, whereas if you put {name}_type first, you will only need to read those that contain that specific type.

what I should have said is that this would be the case if MySql used the index if you put {name}_id first, but of course it doesn't, so it does a scan of the complete table instead which is typically going to be one or two orders of magnitude slower than reading the entire index.