Improved performance for large tables

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 was 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 a estimate is given, the ~ character is show in front of the table row count (see image below #Rows – click to enlarge).

As a plugin user you can change this behaviour:

• Change the Max InnoDB row count (> Settings > WP Data Access > Back-end > Max InnoDB row count)
• Change the behaviour of specific tables (see image below Row count (InnoDB only) – click to enlarge)

SHOW MORE button

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

» Here is a demo using the SHOW MORE button

Thank you Charles for being my sparring partner!