Support » Plugin: Campaign Monitor Forms by Optin Cat » Unnecessary Database queries on each pageload

  • Resolved EvgenyR

    (@evgenyr4wp)


    Hi,
    we’ve noticed some ‘ALTER..’ DB queries run by the plugin on each pageload with the form from the plugin on the page:

    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'id' 'id' INT NOT NULL AUTO_INCREMENT
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'email' 'email' LONGTEXT
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'name' 'name' LONGTEXT
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'time' 'time' DATETIME
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'timezone' 'timezone' LONGTEXT
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'campaign_id' 'campaign_id' INT
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'status' 'status' LONGTEXT
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'consent_granted' 'consent_granted' LONGTEXT
    ALTER TABLE wp_fca_eoi_subscribers CHANGE COLUMN 'consent_msg' 'consent_msg' LONGTEXT

    We had to add manual fix for this, because with thousands of pageviews these DB queries could cause significant load on MySQL DB.

    These queries are caused by a call to dbDelta() function in ‘EoiSubscribers’ class constructor. It really should not be called on each pageload, a variable with a DB table version should be used to check prior to calling to dbDelta() function, see the page in the codex: https://codex.wordpress.org/Creating_Tables_with_Plugins#Adding_an_Upgrade_Function

    Our version of the code with the fix to avoid ALTER DB queries on each pageload:

    $installed_table_ver = get_option( 'fca_eoi_subscribers_ver' );
    
    if ( $this->db_table_ver !== $installed_table_ver ) {
    	//CREATE THE PEOPLE TABLE
    	$sql = "CREATE TABLE $this->table_name (
    		'id' INT NOT NULL AUTO_INCREMENT,
    		'email' LONGTEXT,
    		'name' LONGTEXT,
    		'time' DATETIME,
    		'timezone' LONGTEXT,
    		'campaign_id' INT,
    		'status' LONGTEXT,
    		'consent_granted' LONGTEXT,
    		'consent_msg' LONGTEXT,
    		PRIMARY KEY  (id)
    	);";
    
    	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    	dbDelta( $sql );
    
    	update_option( 'fca_eoi_subscribers_ver', $this->db_table_ver );
    }

    Could you please update the plugin with a similar fix to avoid ALTER DB queries on each pageload?

    Thank you.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author fatcatapps

    (@fatcatapps)

    Hello,

    Thanks for bringing this to our attention.

    I’ve shown this to my developer and we are working on patch already. It just needs to go through some testing, and we hope to have it released later today, or tomorrow.

    I’ll get back to you when we get that out.

    Please let us know if we can help with anything else.

    Regards.

    Hi @fatcatapps,
    thank you for the response!

    How is it going with the update?

    Thank you.

    Plugin Author fatcatapps

    (@fatcatapps)

    Hello again,

    Ok. I’m glad to announce we got an update out late yesterday. We had a bit of a delay when we reached the testing stage.

    But that’s cleared up, and we released after the weekend. So please update your copy of Optin Cat, and let us know how things go!

    And of course let us know if you notice anything else, or have any suggestions.

    Regards

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.