Probleme about cleaning the postsmeta

  • Resolved Tybaze

    (@tybaze)


    8 years, 5 months ago

    I’m having a little problem, my database is currently overloaded with a hundread of thousands of post meta data, and it slow down loading posts.

    The reason is the script which should clean the post metas : (index.php:796)
    "delete from {$wpdb->postmeta} where left(meta_value, 6) = 'wpAjax' and meta_key <> %s" ...

    The meta value is generated by : (index.php:676)
    $rand = '_wpAjax' . $hash . md5( wp_generate_password( 30, true, true ) );

    And using the where condition “left(meta_value,6)” return the string “_wpAja” which never match “wpAjax”, so meta are not cleared.

    ————————————————-
    Suggestion 1
    I think the meta_key could lead to overwritten data by another plugin. The key with _$comment_id is probably used by a lot of others plugins.
    I recommand the use of a more complexe prefix like “_sce_”.

    Meta tables are optimized with index over the meta_key field, by choosing a prefix, we could use the index later (cf 3)
    ————————————————–
    Suggestion 2
    Why not replacing the postmeta with a commentmeta.
    The authorisation of editing a comment is directly linked to the comment.
    ————————————————–
    Suggestion 3
    I’m not sure to understand exactly the role of the option “ajax-edit-comments_security_key_count”.
    If this is only to remove meta that have timedout, I suggest to add the timestamp directly into the meta value, example : $hash = _wpAjax[md5X2]-123456789
    With 123456789 the timestamp of the comment post.

    And then to remove all expired data, you just have to call this kind of query :

    DELETE FROM
wp_postmeta
WHERE
meta_key LIKE '_sce_%' AND
CAST( SUBSTRING(meta_value, LOCATE('-',meta_value ) +1 ) AS UNSIGNED) < ?

    with
    ? = (time() - $this->comment_time)

    I hope this will help you, and your plugin is really smart.

    https://www.ads-software.com/plugins/simple-comment-editing/

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

    (@ronalfy)

    Thanks. I’ll ponder how to do this in the next version with your suggestions.

    Plugin Author Ronald Huereca

    (@ronalfy)

    I think I’ll go with a mixture of all 3 suggestions.

    I’ll check for the presence of ajax-edit-comments_security_key_count. If it exists, I’ll delete all the old meta and remove the option.

    From now on when saving the security hash, I’ll use comment meta with the sce prefix.

    I’ll then run the query you suggested in #3 as a time transient every 6 hours.

    Thanks for the feedback and sorry for taking so long to get back to you. Personal issues unfortunately.

    Plugin Author Ronald Huereca

    (@ronalfy)

    I’ve implemented your changes if you have time to test it out.

    https://github.com/ronalfy/simple-comment-editing

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Probleme about cleaning the postsmeta’ is closed to new replies.