I have a MySQL view that loads about 40,000 records. My table is set to server side. I currently have 8 filters set to load ALL distinct data. My table is set to load 25 rows at a time.
As I watch the table load, it queries for about 10 minutes and the page then gives up. Is there a special setting to get pagination to work more efficiently? Assuming this is actually loading 25 records, I would expect that it would not take this long. The only area where I could try to eliminate the load time, if pagination is working correctly is with the filters. It appears as though each filter has to load separately, one query at a time since they are dynamically loaded.
Are there any recommendations you can make to make this faster? Does server-side truly only load 25 records at a time, or does using filter negate that feature in that it needs to load all distinct records for each filter?
I disabled all filters and the table actually loads now. How can filtering on large datasets with many multiselect filters (showing all multiselect values) be achieved without slowing down the load time?
For example, with server-side and filters enabled on this dataset, setting 1 column filter to load 10 records in a multiselect control causes a 20 second delay for the control to load all distinct options, then choosing the filter takes another 30 seconds to load the new dataset.
Alternatively, If I set the filtered field to load All, then I can at least get the filter to work, but the dataset still takes a long time to load.
Lastly, when the dataset finally loads, and I click to view page 2 of the the paginated results, it does this all over again. Here is an example query for a table that loads 25 rows of 40,000:
SELECT SQL_CALC_FOUND_ROWS * FROM MySQL_View ORDER BY `Modified` DESC LIMIT 25 OFFSET 25
Should I move to ajax based loading as opposed to direct mysql? Would that help? Is there a better way to do what I need with a large dataset?
Well, my advice would've been to change the number of possible values to load from "10" to all, so there's no search in the filter. That speeds it up, like you've seen for yourself.
The speed in general depends on a lot of things - Service Provider, Server Performance, local machine performance, internet speed, etc.
If I understood you correctly, the query which returns only 25 rows of a 40,000 row table runs this slow? That shouldn't be happening, and I'd like to take a look at your table to see how it behaves in back-end.
Please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.
I have a MySQL view that loads about 40,000 records. My table is set to server side. I currently have 8 filters set to load ALL distinct data. My table is set to load 25 rows at a time.
As I watch the table load, it queries for about 10 minutes and the page then gives up. Is there a special setting to get pagination to work more efficiently? Assuming this is actually loading 25 records, I would expect that it would not take this long. The only area where I could try to eliminate the load time, if pagination is working correctly is with the filters. It appears as though each filter has to load separately, one query at a time since they are dynamically loaded.
Are there any recommendations you can make to make this faster? Does server-side truly only load 25 records at a time, or does using filter negate that feature in that it needs to load all distinct records for each filter?
I disabled all filters and the table actually loads now. How can filtering on large datasets with many multiselect filters (showing all multiselect values) be achieved without slowing down the load time?
For example, with server-side and filters enabled on this dataset, setting 1 column filter to load 10 records in a multiselect control causes a 20 second delay for the control to load all distinct options, then choosing the filter takes another 30 seconds to load the new dataset.
Alternatively, If I set the filtered field to load All, then I can at least get the filter to work, but the dataset still takes a long time to load.
Lastly, when the dataset finally loads, and I click to view page 2 of the the paginated results, it does this all over again. Here is an example query for a table that loads 25 rows of 40,000:
SELECT SQL_CALC_FOUND_ROWS * FROM MySQL_View ORDER BY `Modified` DESC LIMIT 25 OFFSET 25
Should I move to ajax based loading as opposed to direct mysql? Would that help? Is there a better way to do what I need with a large dataset?
Hello jko5er.
Well, my advice would've been to change the number of possible values to load from "10" to all, so there's no search in the filter. That speeds it up, like you've seen for yourself.
The speed in general depends on a lot of things - Service Provider, Server Performance, local machine performance, internet speed, etc.
If I understood you correctly, the query which returns only 25 rows of a 40,000 row table runs this slow? That shouldn't be happening, and I'd like to take a look at your table to see how it behaves in back-end.
Please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables