elementindex.twig / custom elements performance in plugin best practices

[MatthiasBrodelet]

I have created a custom plugin which has its own database table and a custom element.
This all works as intended but I notice a big slowdown in performance when looking at the overview page which extends the '_layouts/elementindex' template.
I have already added indexes in the database but this has not fixed the issue completely.
The performance difference in the Craft Commerce orders overview compared to my plugin is enormous.

What are some best practices in this case?

[brandonkelly]

What data are you showing on the index, besides the heading column? Anything that is causing additional n+1 queries?

[MatthiasBrodelet]

@brandonkelly Why didn't I think of that :)

You are right, there are some column fields which do some additional requests.
For example, looking up the name of a referenced entry based on the UID.
Each row will fetch the entry with that UID using Entry::find()->uid($this->reference)->one();.

I guess I can add a cache tag to this query to speed it up for the next ones?

[brandonkelly]

Querying entries by UID is not very performant since we don’t have an index on that column (and adding one would be pretty expensive.) If you set your references to entry IDs instead, that would improve things immediately, plus then you could start eager-loading the entries via your element’s prepElementQueryForTableAttribute() method.

[MatthiasBrodelet]

Sounds perfect.
I already have 350 entries which have a link using the UID.
Is it safe to create a migration to convert all those UID's to ID's?

[brandonkelly]

Sure.