Large tables and performance

Three ways to improve performance! There are more…

Estimated row count

Tables are using pagination by default. Although pagination improves the user experience, it slows down performance, especially for large tables. Pagination requires counting:

All table rows
All matching table rows

These counts consume resources and can slow down performance.

A row count estimate can be used to prevent row counts on every page view. Calculated and hard estimates can be defined for InnoDB tables, federated tables and views in the Data Explorer. The following summary information is shown for tables having the estimated row count enabled.

Without a filter:

~Showing 1 to 10 of 12,565,527 entries

With a filter:

~Showing 1 to 10 of 7017 entries (filtered from 12,565,527 total entries)

The estimated row count feature only eliminates the first row count mentioned earlier. The second row count (with filter) is still needed to show the number of entries (see information summary above). Unfortunately, this is mostly the most resource consuming query of all.

The Data Publisher supports option wpda_use_estimates_only to prevent this query as well. This option elimates the second large table horror count at the cost of incomplete summary information. As a result, queries on a table containing > 12.5 millions rows are lightning fast, even with a filter. But the summary information always shows:

~Showing 1 to 10 of 12,565,527 entries

And does not contain the number of rows filtered.

The following advance table setting will force the plugin to use only estimates:

{
	"wpda_use_estimates_only": true
}

NOTE

This option is only available for the Data Publisher. It does not work in the Data Explorer and Data Projects.

Force user to press ENTER on new search

A search box is added to a publication by default. When the user enters text in the search box, the plugin sends a request on every key press and updates the table immediately. This increases network traffic and can slow down performance. Use option wpda_search_force_enter to prevent the plugin sending a request on every key pressed. The following advance table setting will force the user to press the enter key for every search:

{
	"wpda_search_force_enter": true
}

The JavaScript code for this function was written by Charles Godwin. Charles is also the author of the alternative search algorithm extension.

SHOW MORE button

The SHOW MORE button disables pagination completely. This also elimates the two earlier mentioned count statements, and can thereofr be regarded as an alternative for the estimated row count feature, especially with large tables. The SHOW MORE button feature is part of the free version.

Inline demo

ID	First Name	Last Name	Email	Birthdate	Added
ID	First Name	Last Name	Email	Birthdate	Added

Enable SHOW MORE button

The plugin adds pagination to publications by default. To switch from pagination to the SHOW MORE button:

Disable checbox “Allows paging?” in your publication
Add the following option to Table options (advanced):

{
	"serverSide": true
}

Change info message

The plugin adds a default info message to the bottom of the table like:

10 rows selected (from 44,590 entries)

Add the following option to table options advanced to change this info message:

{
	"wpda_search_more_info": "_END_ rows selected…"
}

Please enter your own text. The following tokens are supported: _START_ _END_ _MAX_ _TOTAL_

For InnoDB tables and views with an estimated row count higher than your actual Max row count, the info message shows a -1 value for the total number of rows. Change the Row count in Table Settings to Show estimated row count to force the plugin to use the estimated row count. Read more…

Change button labels

Use the following options to change the labels on the SHOW MORE and the END OF LIST buttons.

{
	"show_more_text": "> SHOW MORE <",
	"end_of_list_text": "> END OF LIST <"
}

Be careful

With checkbox “Allow paging?” disabled, the plugin loads the whole table at once. For large tables this might result in an error. Make sure Table options (advanced) contains the option mentioned above for large tables before you test!

Index