Creating MySQL-based tables with server-side processing

Video versionA video overview of creating MySQL-based wpDataTables with Server-side processing

wpDataTable MySQL server-side processing

This tutorial, and this wpDataTable creation method, assumes 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 in 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 server side and the page load and initialization time on 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 MySQL-based 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 the MySQL performance as well, this is it.

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

In this tutorial, we will use a data set of 10.000 rows; you can download the SQL dump using this link. A table without server-side processing with 10.000 rows would typically require 3-5 minutes for loading (if it would load 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.

If the result of MySQL query is quite large (hundreds of thousands, or even milions of rows), and the MySQL queries and tables are not optimized properly (with indexes and proper data types), the tables can still work slow – but wpDataTables isn’t “responsible” for this since the delays are derived from the MySQL engine. Optimize the MySQL tables and queries structure, and wpDataTables will speed up as well!

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 “Import” tab, go to “Choose file“, open the file that you just downloaded, and click “Go” so that phpMyAdmin will import the table.

Displaying large MySQL tables in WordPress 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 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:

Create a table linked to an existing data source

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

Creating MySQL-based tables with server-side processing

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 selectbox.
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 Apply, so that wpDataTables will initialize the table and columns metadata from the results of this query.

(Optional) - Define additional table and columns settings

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

Inserting wpDataTables shortcode on page or post

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 want to use Gutenberg editor, follow the guide from this link.

Do you have the best

essential plugins installed?

Grab the best 46 plugins. Tested and sorted.

Plugins for SEO, Caching, Security, Design and UX, Stats & analytics, Social sharing. Only the best ones.

By clicking "subscribe" you consent that the information you provide via this form will be securely processed and stored in compliance with EU privacy laws (GDPR).

Thank you for subscribing! Please check your inbox to download the WordPress goodies.