Improved performance for large tables

by Mar 26, 2021Plugin, What’s new?0 comments

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

The pagination features were optimized in version 4.2 and does no longer count all table rows on each request:

  • For MyISAM tables the table row count this value is taken from the MySQL data dictionary
  • For InnoDB tables the table row count is taken from the MySQL data dictionary if:
    • The estimated table row count exceeds the Max InnoDB row count
    • This is defined for a specific table
A table row count is only performed on InnoDB tables if the criteria mentioned above are not met. This improved search strategy was added to all WP Data Access tools.
As a result, the row count for InnoDB tables might be an estimate. The real table row count might be different. Whenever an estimate is given, the ~ character is shown in front of the table row count (see image below #Rows – click to enlarge).
As a plugin user, you can change this behavior:
  • Change the Max InnoDB row count (> Settings > WP Data Access > Back-end > Max InnoDB row count)
  • Change the behavior of specific tables (see image below Row count (InnoDB only) – click to enlarge)
WP Data Access - Data Explorer Table Settings

SHOW MORE button

The SHOW MORE button is a new feature added to Data Tables. Pagination can now be disabled per publication without the need to show the whole table at once. The SHOW MORE button eliminates both count actions, the table row count and the matching row count.

Thank you Charles for being my sparring partner!


Submit a Comment

Your email address will not be published. Required fields are marked *