{"id":18989920,"date":"2026-08-10T23:21:53","date_gmt":"2026-08-10T23:21:53","guid":{"rendered":"https:\/\/wordpress.org\/support\/topic\/wp-ulike-database-optimization-suggestions\/"},"modified":"2026-08-10T23:21:53","modified_gmt":"2026-08-10T23:21:53","slug":"wp-ulike-database-optimization-suggestions","status":"publish","type":"topic","link":"https:\/\/wordpress.org\/support\/topic\/wp-ulike-database-optimization-suggestions\/","title":{"rendered":"WP ULike Database Optimization Suggestions"},"content":{"rendered":"\n

First of all, thank you for your great work on developing and maintaining WP ULike.<\/p>\n\n\n\n

I would like to share some suggestions regarding database performance and storage efficiency, particularly for high-traffic websites.<\/p>\n\n\n\n

Our ulike_pulse<\/code> table has currently grown to over 5 million rows<\/strong>, occupying nearly 3 GB of storage<\/strong>. After analyzing the table structure, it appears that a significant portion of this size comes from repeated long text values and anti-spam metadata that is stored indefinitely for every engagement.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To improve scalability and database efficiency, I would like to suggest the following optimizations for future versions:1. Data Retention Policy for Non-Essential Metadata<\/p>\n\n\n\n

Fields such as fingerprint<\/code>, dedupe_token<\/code>, browser<\/code>, and os<\/code> are primarily useful for anti-spam and security purposes at the time of engagement.<\/p>\n\n\n\n

After a certain period, such as 30, 60, or 90 days, this information may no longer be operationally necessary.
<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Suggestion:<\/strong> Add an option in the plugin settings to automatically clear or purge non-essential metadata from records older than a configurable number of days, while keeping the core engagement record intact.2. Optimize Column Data Types<\/p>\n\n\n\n

Columns with a limited number of possible values could use more storage-efficient data types such as TINYINT<\/code> or ENUM<\/code> instead of longer strings.<\/p>\n\n\n\n

For example, fields such as device<\/code>, status<\/code>, and engagement_kind<\/code> could potentially be represented using compact numeric values.3. Optimize Like\/Dislike Values with Numeric\/Boolean Types<\/p>\n\n\n\n

Currently, values such as engagement_key<\/code> may store full strings such as 'like'<\/code> or 'dislike'<\/code>.<\/p>\n\n\n\n

Instead, these values could be represented using a compact numeric type such as TINYINT<\/code>, for example:<\/p>\n\n\n\n

    \n
  • 1<\/code> = Like<\/li>\n\n\n\n
  • 0<\/code> or -1<\/code> = Dislike<\/li>\n<\/ul>\n\n\n\n

    This would reduce the storage required for each value to approximately 1 byte<\/strong>, compared with several bytes for the corresponding string representation.<\/p>\n\n\n\n

    Across millions of records, this relatively simple change could substantially reduce the overall table size as well as the size of related indexes.4. Store IP Addresses More Efficiently<\/p>\n\n\n\n

    If IP addresses are currently stored as text (VARCHAR<\/code>), they could potentially be stored using INT UNSIGNED<\/code> for IPv4 addresses, with INET_ATON()<\/code> and INET_NTOA()<\/code> for conversion.<\/p>\n\n\n\n

    This requires only 4 bytes per IPv4 address<\/strong>, reducing storage compared with text-based representation.5. Normalize Browser and Operating System Data<\/p>\n\n\n\n

    Storing complete browser and operating system strings repeatedly across millions of records can create significant database overhead.<\/p>\n\n\n\n

    Suggestion:<\/strong> Store unique browser\/OS combinations in a separate lookup table, such as ulike_user_agents<\/code>, and reference them from the main table using a compact integer user_agent_id<\/code>.6. Separate Core Engagement Data from Security\/Audit Data<\/p>\n\n\n\n

    Another possible optimization would be to separate essential engagement data from heavier anti-spam and security information.<\/p>\n\n\n\n

    For example:<\/p>\n\n\n\n

    Core table (<\/strong>ulike_engagements<\/strong><\/code>):<\/strong><\/p>\n\n\n\n

      \n
    • id<\/code><\/li>\n\n\n\n
    • user_id<\/code><\/li>\n\n\n\n
    • item_id<\/code><\/li>\n\n\n\n
    • item_type<\/code><\/li>\n\n\n\n
    • date_time<\/code><\/li>\n\n\n\n
    • engagement_type<\/code><\/li>\n<\/ul>\n\n\n\n

      Audit\/security table (<\/strong>ulike_engagement_logs<\/strong><\/code>):<\/strong><\/p>\n\n\n\n

        \n
      • fingerprint<\/code><\/li>\n\n\n\n
      • dedupe_token<\/code><\/li>\n\n\n\n
      • ip<\/code><\/li>\n\n\n\n
      • browser<\/code><\/li>\n\n\n\n
      • other anti-spam metadata<\/li>\n<\/ul>\n\n\n\n

        This would keep the primary engagement table lightweight and make the most frequently used queries and indexes more efficient.7. Built-in Archiving and Maintenance Tools<\/p>\n\n\n\n

        It would also be useful to provide a WP-CLI command or background cron job that allows administrators to archive or clean up historical engagement data in bulk.<\/p>\n\n\n\n

        These database optimizations could significantly reduce storage requirements, index sizes, memory usage, and query overhead on high-traffic WordPress websites.<\/p>\n\n\n\n

        I hope these suggestions can be considered for future versions of WP ULike. They could be particularly beneficial for websites with millions of engagement records.<\/p>\n\n\n\n

        Thank you very much for your time and for your continued work on the plugin.<\/p>\n","protected":false},"template":"","class_list":["post-18989920","topic","type-topic","status-publish","hentry"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/18989920","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic"}],"about":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/types\/topic"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/18989920\/revisions"}],"wp:attachment":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/media?parent=18989920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}