Maybe optimize lookups of tables in DB to stop using SHOW TABLES LIKE

[Khadreal]

One of our customers reported a performance issue related to berlindb especially for sites with large tables. This is related to SHOW TABLES... query in.

core/src/Database/Table.php

Line 351 in 9c4d1fe

public function exists() {

The proposed solution
Changed the query to below, we could see improved performance when we do this. we've forked the repo but before creating a PR we thought opening a ticket here to know if there are other things we aren't considering with the solution.

SELECT table_name FROM information_schema.tables WHERE table_name = '%s' LIMIT 1

[alexstandiford]

I'm actually going to be working with BerlinDB for a project sometime soon. While I've got it cloned I'll take a look at the PR, too.

Thanks for sharing!

[wordpressfan]

@JJJ can we please do a release of berlindb/core with all of those merged changes? the last release date was from 2 years or more :D and we use composer to update ur package.
Thanks Boss.

[wordpressfan]

@JJJ Still can't see the release here:
https://github.com/berlindb/core/releases
Is it possible to do that release so we can get it in our composer?

[JJJ]

This will need to be revisited when SQLite support becomes a priority, because information_schema does not exist in SQLite.

[JJJ]

Still considering reverting this.

See: wp-media/wp-rocket#7818

[wordpressfan]

🧩 The Situation
Our plugin runs:

SELECT table_name FROM information_schema.TABLES WHERE table_name = 'wp_table_name' LIMIT 1;

and it becomes very slow (≈10 seconds) after you enable ModSecurity.

But this one:

SHOW TABLES LIKE 'wp_table_name';

is instant.

🧠 Why this happens

1. The information_schema query triggers ModSecurity inspection
   When ModSecurity is enabled for Nginx or Apache, it can inspect SQL queries (depending on the CRS ruleset).
   Many OWASP CRS rules flag access to information_schema as potential SQL injection reconnaissance — because attackers often query it to enumerate database metadata.

So when our plugin executes that SELECT ... FROM information_schema.TABLES, ModSecurity doesn’t block it, but it:

• logs it,
• scans it against all SQLi-related regex patterns,
• sometimes waits for backend confirmation (depending on SecAuditEngine mode).

That extra inspection causes the delay you’re seeing.
Essentially, the query triggers a “suspicious query inspection” → ModSecurity takes time analyzing → delay.

2. information_schema is slower in MySQL/MariaDB anywayEven without ModSecurity:information_schema.TABLES is a virtual table that’s regenerated dynamically.
   It causes metadata locking and full table scans internally.
   So combining that with ModSecurity deep inspection = major delay.

3. SHOW TABLES LIKE bypasses both problems
   SHOW TABLES doesn’t touch information_schema and doesn’t look suspicious to ModSecurity,
   so:
   ModSecurity doesn’t flag it,
   MySQL answers instantly from cached table metadata.
   Hence: fast.

So I believe we need to revert this but the question here is:
We will face the old issue with big sites and for this what do u think if we used sort of caching? (suggestion from @jeawhanlee )?

[JJJ]

We will face the old issue with big sites and for this what do u think if we used sort of caching?

The way Berlin does this currently is via methods in the Table class:

core/src/Database/Table.php

Lines 875 to 907 in ccc6f8b

	/**
	* Set the database version for the table.
	*
	* @since 1.0.0
	*
	* @param mixed $version Database version to set when upgrading/creating
	*/
	private function set_db_version( $version = '' ) {
	// If no version is passed during an upgrade, use the current version
	if ( empty( $version ) ) {
	$version = $this->version;
	}
	// Update the DB version
	$this->is_global()
	? update_network_option( get_main_network_id(), $this->db_version_key, $version )
	: update_option( $this->db_version_key, $version );
	// Set the DB version
	$this->db_version = $version;
	}
	/**
	* Get the table version from the database.
	*
	* @since 1.0.0
	*/
	private function get_db_version() {
	$this->db_version = $this->is_global()
	? get_network_option( get_main_network_id(), $this->db_version_key, false )
	: get_option( $this->db_version_key, false );
	}

The existence of the database version as a WordPress option is the fastest way to know that the table should already exist. It's easy to query for directly, and cached in a persistent way if available.

The only way to verify 100% that the table exists is to query something for it directly.

And the only way the WordPress option exists and the table doesn't, is if the table was manually dropped or deleted outside of Berlin's uninstall() method.

Once the database table exists, the exists() query will only ever run when the version changes, most likely to intentionally modify the schema/structure.

core/src/Database/Table.php

Lines 214 to 241 in ccc6f8b

	/**
	* Maybe upgrade the database table. Handles creation & schema changes.
	*
	* Hooked to the `admin_init` action.
	*
	* @since 1.0.0
	*/
	public function maybe_upgrade() {
	// Bail if not upgradeable
	if ( ! $this->is_upgradeable() ) {
	return;
	}
	// Bail if upgrade not needed
	if ( ! $this->needs_upgrade() ) {
	return;
	}
	// Upgrade
	if ( $this->exists() ) {
	$this->upgrade();
	// Install
	} else {
	$this->install();
	}
	}

We could just trust that the WordPress option existing means the table exists, skip checking for it, and just let the upgrade() methods fail if there is no table.

If we go that route, Table would benefit from some kind of health() or debug() method to help us devs see what's going on.

[JJJ]

Could also try this:

Suppress errors, then...

SELECT 1 FROM {$this->table_name} LIMIT 0;

Then check $wpdb->last_error for errors.

[JJJ]

Reverted via 6a9383a.

[wordpressfan]

For depending on the version option plz check my comment here:
wp-media/wp-rocket#7818 (comment)