Title: performance issue on large database
Last modified: February 9, 2022

---

# performance issue on large database

 *  Resolved [ensemblebd](https://wordpress.org/support/users/ensemblebd/)
 * (@ensemblebd)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/performance-issue-on-large-database/)
 * Am seeing a significant performance drop due to this plugin. Love the plugin,
   and it’s fantastically well written.
    But there are two queries that simply tank
   wordpress. And because it’s mysql, when one query takes 2.5 to 6 seconds to finish,
   it snowballs the entire server. I’m seeing on linux CPU usage percentages over
   700%.
 * includes/class-custom-permalinks-frontend.php
    line #164 and #182. The ORDER 
   BY LENGTH(meta_value) and the (meta_value = %s OR meta_value = %s) in where clause
   are to blame. Since the meta_value column is not indexed. Nor should it be given
   the values it contains.
 * It’s so bad that I’m about to have to write a custom cache for it. the wp_cache_get
   can’t do the job. Not even if you use the Redis plugin for it.
    My current plan
   is to hash the meta_values as integers, in a custom mysql table that contains
   the SELECT list of values (post ID, meta ID, post type, post status, and hash).
   That way this code has an instantaneous lookup on an indexible column. No joins,
   no reordering, no text comparisons. Which will require a hook into the update
   op of any entry of course to keep it in sync.
 * Not sure if you’ve seen this issue before. But since the code mentioned runs 
   on every single page load, it’s practically a ddos level event.
    -  This topic was modified 4 years, 3 months ago by [ensemblebd](https://wordpress.org/support/users/ensemblebd/).

Viewing 4 replies - 1 through 4 (of 4 total)

 *  Thread Starter [ensemblebd](https://wordpress.org/support/users/ensemblebd/)
 * (@ensemblebd)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/performance-issue-on-large-database/#post-15348216)
 * 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;
       }
       ```
   
 *  [Pratham](https://wordpress.org/support/users/pratham2003/)
 * (@pratham2003)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/performance-issue-on-large-database/#post-15355216)
 * I support this idea.
    Take away: Use a custom table when it gives performance
   benefits.
 * Don’t fall into the trap of preferring standard tables because your or one of
   the other plugin bloats up the options/postmeta table and the entire website 
   becomes slow.
 * Ref on similar issues: [https://core.trac.wordpress.org/ticket/14558](https://core.trac.wordpress.org/ticket/14558)
 *  Plugin Author [Sami Ahmed Siddiqui](https://wordpress.org/support/users/sasiddiqui/)
 * (@sasiddiqui)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/performance-issue-on-large-database/#post-15456026)
 * [@ensemblebd](https://wordpress.org/support/users/ensemblebd/) Thank you for 
   sharing your solution. The idea to save permalinks in a separate table is already
   in consideration from last year. It will be a big change that might affect couple
   of existing site so it takes time to be released.
 * Regards,
    Sami
 *  [seliyu](https://wordpress.org/support/users/seliyu/)
 * (@seliyu)
 * [4 years, 1 month ago](https://wordpress.org/support/topic/performance-issue-on-large-database/#post-15542403)
 * [@ensemblebd](https://wordpress.org/support/users/ensemblebd/) Can you provide
   me with a complete version of the modification of this plugin? I have tried to
   modify the files as described, but it doesn’t work for me.
 * Thanks in advance

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘performance issue on large database’ is closed to new replies.

 * ![](https://ps.w.org/custom-permalinks/assets/icon.svg?rev=1785367)
 * [Custom Permalinks](https://wordpress.org/plugins/custom-permalinks/)
 * [Support Threads](https://wordpress.org/support/plugin/custom-permalinks/)
 * [Active Topics](https://wordpress.org/support/plugin/custom-permalinks/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/custom-permalinks/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/custom-permalinks/reviews/)

 * 4 replies
 * 4 participants
 * Last reply from: [seliyu](https://wordpress.org/support/users/seliyu/)
 * Last activity: [4 years, 1 month ago](https://wordpress.org/support/topic/performance-issue-on-large-database/#post-15542403)
 * Status: resolved