Table Builder doesn't work with 70K translatable data entries because of default ordering

Created 5 years ago by fatihalp

I've 70K village data, it takes 833 seconds to display 15 data on admin panel. Most of time it gives an timout. Query that table builder is generating is below. If you remove ordering and grouping part of SQL, it takes only 0.01 seconds. How to remove GROUP and ORDER statements?

SELECT default_advs_village.* FROM default_advs_village LEFT JOIN default_advs_village_translations ON default_advs_village.id = default_advs_village_translations.entry_id WHERE ( default_advs_village_translations.locale = 'en' OR default_advs_village_translations.locale = 'en' OR default_advs_village_translations.locale IS NULL ) GROUP BY default_advs_village.id ORDER BY default_advs_village_translations.name ASC LIMIT 15 OFFSET 0

ryanthompson — 5 years ago

Hey there! Would you be open to finding me on Slack and sharing your dataset? Sounds like a good sample size to test some indexes and performance around it for core.

The grouping / ordering is done because your stream's title field is translatable I believe. It's done automatically. However I think it's only for postgres support which I've been considering dropping because of other issues around it.

Link

fatihalp — 5 years ago

Thanks to Ryan we solved problem by adding:

  /**
     * Fired just before starting the query.
     *
     * @param Builder $query
     */

    public function onQuerying(Builder $query)
    {
        $query->orderBy("id");
    }

Link