Server-side processing (Lazy Loading) in wpDataTables

“Server-side processing” stands for table types when the data interaction operations (searching, filtering, switching pages, sorting) aren’t done by your site visitor’s browser (with JS), but instead by the MySQL server on your site.

One of the main benefits of the MySQL DB engine is fast data processing. When you need to sort an entire table by one of the columns, or filter it by some value, it will be done quickly, even if it contains several hundred, a thousand, or even millions of records. What is the key difference between “usual” wpDataTables and wpDataTables with server-side processing turned on? When should the first and the second option be used?

• “Usual” wpDataTables (the ones that do not have the server-side processing feature enabled) fetch all the rows, output the entire table to the front-end all at once, and then split it into pages using JavaScript; all data processing (page switching, sorting, filtering) is done on the client’s computer by JavaScript. While this works great for relatively small tables, once a table gets bigger, the page also gets larger. Consequently, page generation and load time will also increase correspondingly, and sorting, filtering, or page switching will take more time. So, for larger data sets (more than 2000-3000 rows, and in some installations more than 500 rows), the “usual” wpDataTables will work more slowly, and for really large data sets (10 000 rows and more) the page can crash completely.
• wpDataTables with server-side processing enabled fetches only those rows needed on the page at the exact moment. By default, it equals to the number of rows that administrator defines for the table in the “Display length” setting; the front-user can however, change it. For example: if the table is configured to show 10 rows by default, only 10 rows would be queried from MySQL. When the user switches to the next page, and sorts the table by some column, or filters by some column, an AJAX-request is sent to the server, the query is processed by MySQL, and 10 more rows are returned. This may slow down the processing, search, filtering and sorting (of smaller tables also), but for larger tables it may be the only option.

There’s no “official” rule for table size from different input sources, as it greatly depends on hosting, bandwidth, and the device of the front-end user; but, in general, file-based (Google Spreadsheet,Excel, CSV, XML, JSON) tables can freely be used for tables that have no more than 2000-3000 cells (columns * rows). The MySQL table type can be used to any limit, but the server-side processing feature is mandatory for data sets exceeding 3000-4000 rows.

If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, it is likely happening because the wpDataTables server has problems parsing the query and building new queries dynamically (this rarely happens, but nevertheless, it can happen). To avoid this, prepare a MySQL view (a stored query), which will return the data you need, call it for example “view1”, and then build a wpDataTable based on a simple query like “SELECT * FROM view1″.

Please note when working with the server-side processing feature:

• Do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically, and it will be overridden.
• Do not use “ORDER BY” in the SELECT statement. Since wpDataTables has its own sorting engine, it makes no sense to use MySQL’s sorting, since it will be overridden. Also, the server-side processing feature adds this part of the statement automatically when a user triggers the sorting on the front-end, and including it in the initial statement can cause the table to crash.
• Server-side processing will turn on automatically when your SQL query based table exceeds 2.000 rows, and it cannot be disabled for tables with over 2.000 rows.