Creating MySQL-based tables with server-side processing

Table of Contents

Video tutorial

wpDataTable MySQL server-side processing

This tutorial, and this wpDataTable creation method, assume you have at least some knowledge about SQL, and you are able to prepare the data on the MySQL side and are able to fetch it with a query.

When your data set is larger than a couple of thousand rows, it can’t effectively be loaded onto the page from a Google Spreadsheet, a CSV or Excel file, or other sources. Each of these methods first reads the data from the source, and then prints out the complete table data on your page; so, as the row count grows, it makes both the page generation time on the server side and the page load and initialization time on the client side, slower and slower. If your host has a certain memory or timeout limit defined for PHP scripts, it can ‘break’ the page, because the script would try to allocate more memory than it’s allowed. But no worries! wpDataTables has a solution for that – datatables server-side processing!

Server-side processing is available only for tables saved in the database (SQL tables); consequently, all filtering, sorting, pagination, and other data processing routines will be delegated to the MySQL engine, and only a smaller number of rows will be fetched from the server by a background AJAX request at a time. If you wanted to find out how to improve SQL performance, this is it.

Please refer to this documentation section for a full explanation of the Server-side processing feature.

This tutorial will use a data set of 2.560.000 rows; you can download the SQL dump sample (only 10.000 rows, because the whole dump is very large) using this link. A table without server-side processing with 2.560.000 rows would typically require 5-15 minutes for loading (if it loaded at all). Let’s see how it performs with server-side processing enabled. Just try to filter or sort the table.

The step-by-step tutorial on how to create one of these huge tables with no hassle.

id	Name	Company	Order Date	Product	Amount	Total
1	Yoshio W. Roach	Cakewalk	30/10/2018	Product Five	10	136.00
2	Sawyer M. Good	Cakewalk	26/11/2016	Product Seven	1	1,806.00
3	Flynn M. Bryant	Sibelius	23/09/2017	Product Four	4	154.00
4	Stephanie Q. Robles	Adobe	25/03/2018	Product Three	1	1,576.00
5	Lacey T. Rocha	Borland	10/03/2017	Product Five	7	1,990.00
6	Garrison J. Foreman	Adobe	04/04/2018	Product Three	3	810.00
7	Maisie M. Park	Macromedia	21/02/2018	Product Five	10	1,202.00
8	Salvador Y. Jennings	Lycos	01/05/2018	Product Two	1	834.00
9	Valentine D. Knight	Cakewalk	18/12/2016	Product Six	3	1,028.00
10	Abdul L. Brewer	Borland	17/01/2018	Product Six	3	1,100.00
id	Name	Company	Order Date	Product	Amount	Total

If a MySQL query returns a lot of data (hundreds of thousands, or even millions of rows), and if the queries and tables are not appropriately optimized (with indexes and proper data types), the tables can still work slowly – but wpDataTables isn’t “responsible” for this since the delays are derived from the MySQL engine. Optimize the MySQL tables and query structure, and wpDataTables will also speed up!

Instructions to create the table

Displaying large MySQL tables in WordPress with wpDataTables

The first thing to be done is to prepare the data on the MySQL side. Normally, you would already have tables and the query but here we will assume that you use the same sample table as in the example on this page.

Here’s the link to download the SQL dump of this table.

Go to your phpMyAdmin, choose the database that wpDataTables is configured to use, open the “Import” tab, go to “Choose file“, open the file that you just downloaded, and click “Go” so that phpMyAdmin will import the table.

Table with million rows with wpdatatables

You can then click on the “sample_large_table” in the tables browser on the left to verify if the data loaded correctly.

Once the data is loaded, we need to prepare an SQL query that will fetch this data from MySQL. In this case, it’s simple: “SELECT * FROM sample_large_table“. You can try this query out in the SQL tab of PHPMyAdmin to see that it returns the information we need.

Create a wpDataTable with server-side processing feature enabled

Once the query is prepared, we can build a wpDataTable based on it.

Please open your WordPress admin, and:

1. Go to wpDataTables -> Create a Table, and choose Create a table linked to an existing data source option.

2. Provide a name for your new table in the wpDataTable name input to identify it among others.
3. Choose MySQL query in the Input data source type select-box.
4. Put the SQL query prepared in the step 1 in the MySQL Query editor (“SELECT * FROM sample_large_table“).
5. Make sure that Server-side processing checkbox is enabled.
6. Click Save Changes, so that wpDataTables will initialize the table and columns metadata from the results of this query.

Define additional table and columns settings (Optional)

At this point table is already prepared. But to improve usability, we need to define some additional settings for the table and for its columns.

1. Enabled Limit table width and Word Wrap checkboxes.
2. Disabled the Visible checkbox for “id” column.
3. Defined additional informative and readable Displayed headers for the columns – “Name” instead of “name“, “Order Date” instead of “order_date“, “Product” instead of “product“, “Amount” instead of “amount“, “Total” instead of “total“.
4. Set the Filter type for “Company” as “Select box”.
5. Set the Filter type for “Order Date” column as “Date Range”.
6. Set the Filter type for “Product” as “Select box”.
7. Set the Filter type for “Amount” as a “Number Range”, set the Width to “10%”
8. Set the Filter type for “Total” as “Number Range”, Column type to “Float”, Display text before as “$” so that currency sign would be displayed, and Width to “10%“.

Insert the wpDataTable in WordPress post or Page

When you finish preparing your MySQL-based wpDataTable, you simly need to insert it into your post or page.

1. Create or open a WordPress post or page,
2. Place the cursor in the position where you want to insert your table.
3. Click the “Insert a wpDataTable” button in the MCE editor panel.
4. Choose the MySQL-based table that you prepared from the list and click OK.

If you are using Gutenberg editor, Elementor, or WPBakery page builder, you can check out the section for Adding wpDataTables shortcodes on the page.