Pagination Query for Optimizing 100K+ Rows

[dmsatparam]

Hi! Was wondering how I could paginate each query so I can optimize the table andd prevent loading all 100K data at once? Been looking everywhere for examples and I'm not sure how to move forward. Would it be possible to modify the query from pagination to cursor pagination?

Thanks!

[lrljoe]

The query is paginated by default.

You can set simple pagination instead of you prefer that approach.

public function configure(): void
 {

 $this->setPaginationMethod('simple'); 

}

[dmsatparam]

Appreciate the response, I have set the pagination method to simple a while ago in hopes of having a performance gain. However, simple pagination produces the same loading time at ~3 seconds compared to the normal pagination. From my understanding shouldn't cursor pagination greatly decrease the loading time? If it helps, I am using v2 of the datatable with mySQL.

Thanks!

[lrljoe]

Do you have any filters that are using generated values (e.g. select/multiSelect)?

For the main table, if you look at the resultant queries, what do you see in terms of joins and pagination?
How long does it take the query to run?

If you have a non-prod environment then use debugbar to extract the queries.

I recommend extracting from the first load, make a change to sorting, and extract the second load queries too.

If you can paste them in here then that'll help to understand any issues.

[lrljoe]

I imagine the issue with implementing cursor pagination will be multi-column sorting, particularly where sorting is on json columns or similar as that'll cause some slowness with the db pagination unless specific indexes are there.

I'll take a look at it at some point tho!

[dmsatparam]

Upon reading your answer, I rechecked the filters function and realized that the queries from these fields are the reason why the table is loading slow up to this point. Will now re-assess how to optimize these filters. Appreciate your fast response, thank you very much for helping me!

[lrljoe]

Create a public array variable at the top of your component

E.g.

public array $filter1Options = [];

Use the following in your filter

setOptions($this->filter1Options)

In your configure method

if(empty($this->filter1Options))
{
$this->filter1Options = yourQueryHere
}

This will only load your filter options the one time, massive performance gain.