The WPDataTables plugin automatically adds to the LIKE clause both leading and trailing wildcards to the user text entered in advanced column search boxes and global search boxes. This is a problem for very large MySQL/MariaDB based tables (millions of rows) because B-Tree indexes like composite indexes cannot be utilized when the query includes leading wildcard characters in the LIKE clause. It will force a full table scan if there is a leading wildcard in the LIKE clause of any SELECT query. A full table scan on a table with millions of rows will take a long time to return results.
Add a setting in the table settings or column settings that allows the user to control whether or not a leading wildcard, trailing wildcard, or both leading & trailing wildcard should be included with any or all advanced column or global searches.
Without the above fix, very large tables are almost unusable for search functionality with this plugin because indexes cannot be used.