Adjustments made that resolve this, for your potential consideration.
/includes/class-custom-permalinks.php ::
private function init_hooks() {
//...
register_activation_hook(
CUSTOM_PERMALINKS_FILE,
array( 'Custom_Permalinks', 'add_lookup_table' )
);
//...
add_action( 'save_post', array( $this, 'save_post' ), 10, 3 );
add_action( 'delete_post', array( $this, 'delete_post' ), 10 );
}
public static function add_lookup_table() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_name = $wpdb->prefix . 'cp_lookup';
$sql = "CREATE TABLE IF NOT EXISTS $table_name (
cp_id INTEGER NOT NULL AUTO_INCREMENT,
cp_hash BIGINT NOT NULL,
ID INTEGER NOT NULL,
meta_value VARCHAR(255) NULL,
post_type VARCHAR(20) NULL,
post_status VARCHAR(20) NULL,
PRIMARY KEY (cp_id)
INDEX 'idx_cp_lookup' (cp_id, cp_hash, ID, post_type, post_status, meta_value)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
// fill the lookup table preemptively. Future updates to pages/posts will sync individually.
// useful for existing installations that want to avoid "grandfathering" over time, and start using the improved speed immediately.
public static function fill_lookup_table() {
global $wpdb;
$table_name = $wpdb->prefix . 'cp_lookup';
// cleanup. Shouldn't be necessary, but here for safety, since the hash check we do later demands EXACT matching. And the code from before checks both with and without trailing slash, which would logically indicate that sometimes the meta_value would have a trailing slash..
$wpdb->query("UPDATE $wpdb->postmeta SET meta_value = TRIM('/' FROM meta_value) WHERE meta_key = 'custom_permalink'");
// just in case entries already exist, clean it and start fresh..
$wpdb->query("DELETE FROM $table_name");
// and then populate our lookups from existing data; if any.
$wpdb->query("
INSERT INTO $table_name (cp_hash, ID, meta_value, post_type, post_status)
SELECT
CONV(crc32(pm.meta_value), 16, 10),
p.ID, pm.meta_value, p.post_type, p.post_status
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta pm ON (pm.post_id = p.ID)
WHERE
pm.meta_key = 'custom_permalink'
AND p.post_status != 'trash'
AND p.post_type != 'nav_menu_item'
AND pm.meta_value != ''
");
}
public static function save_post($post_ID, $post, $update) {
global $wpdb;
$table_name = $wpdb->prefix . 'cp_lookup';
$wpdb->query("DELETE FROM $table_name WHERE ID = $post_ID");
$wpdb->query("INSERT INTO $table_name (cp_hash, ID, meta_value, post_type, post_status)
SELECT
CONV(crc32(pm.meta_value), 16, 10),
p.ID, pm.meta_value, p.post_type, p.post_status
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta pm ON (pm.post_id = p.ID)
WHERE
pm.meta_key = 'custom_permalink'
AND p.post_status != 'trash'
AND p.post_type != 'nav_menu_item'
AND pm.meta_value != ''
AND p.ID = $post_ID
");
}
public static function delete_post($post_ID, $post, $update) {
global $wpdb;
$table_name = $wpdb->prefix . 'cp_lookup';
$wpdb->query("DELETE FROM $table_name WHERE ID = $post_ID");
}
public function check_loaded_plugins() {
// ...
if ( is_admin() ) {
$current_version = get_option( 'custom_permalinks_plugin_version', -1 );
if ( -1 === $current_version
|| $current_version < CUSTOM_PERMALINKS_VERSION
) {
self::activate_details();
self::add_roles();
// generate the lookup table on new plugin/db version.
self::fill_lookup_table();
}
}
// ...
/uninstall.php
//...
global $wpdb;
$table_name = $wpdb->prefix . 'cp_lookup';
$wpdb->query("DROP TABLE IF EXISTS $table_name");
//...
/includes/class-custom-permalinks-frontend.php ::
private function query_post( $requested_url ) {
global $wpdb;
$clean_url = urldecode($requested_url);
$hash = hexdec(crc32($clean_url));
$cache_name = 'cp$_' . $hash . '_#cp';
$posts = wp_cache_get( $cache_name, 'custom_permalinks' );
if ( ! $posts ) {
$table_name = $wpdb->prefix . 'cp_lookup';
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
$posts = $wpdb->get_results(
$wpdb->prepare("
SELECT ID, meta_value, post_status, post_type
FROM $table_name
WHERE
cp_hash = %d
ORDER BY
FIELD(post_status,'publish','private','pending','draft','auto-draft','inherit'),
FIELD(post_type,'post','page')
LIMIT 1
", $hash
)
);
wp_cache_set( $cache_name, $posts, 'custom_permalinks' );
}
return $posts;
}
