Plugin reverts all custom indices

  • Resolved Slava Abakumov

    (@slaffik)


    1 month, 2 weeks ago

    Hello there,

    I have added a custom index to the wp_comments table that is specific to the site custom functionality. It improves the speed of the query (that runs quite often) from ~4s to ~100ms. So it’s substantial.

    Now, every plugin update rewrites them effectively dropping my custom index. Not good.

    I do want to add your indices but also keep mine without the need to re-add it every single time.

    Can your plugin instead of dropping all indices – drop only own indices (by name) and recreate them? This way custom one will be left as is.

    Thank you.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author OllieJones

    (@olliejones)

    Thanks for the report. It is good to hear from a user who knows about database keys. Sorry for the inconvenience!

    You are correct about this plugin removing other indexes. I did that because some early users had messy indexes that needed to be cleaned up.

    We have a way built-in to ignore certain indexes. If you give your index a name starting with index_wp_mysql_protect_ this plugin will not touch it. The same goes for index names starting with woo_, crp_ , and yarpp_ .

    Alternatively, if you want to manage your own indexes you can get this plugin to show you the data-definition-language commands to change the keys. Use wp-cli and say this to get it to emit the commands and not actually run them.

    wp index-mysql enable --all --dryrun

    What index have you added? If you think it’s widely useful, maybe we could add it to this plugin? Please let me know.

    Thread Starter Slava Abakumov

    (@slaffik)

    It’s this one:

    ALTER TABLE wp_comments
    ADD INDEX comment_user_type_approved (user_id,comment_type,comment_approved) 
    USING BTREE;

    Which is optimizing the query to receive a number of approved comments (by a certain type as there are several types) by a certain user. Personally, it’s quite specific and I don’t think worthy to be included into the plugin, but that’s your call.

    Using the index_wp_mysql_protect_ prefix is fine, I will just rename my index to index_wp_mysql_protect_user_type_approved. Thanks for the tip!

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